# Appendix B. Driver and Database Characteristics

#### Contents:

Acquiring the DBI and Drivers

In this appendix, we hope to give you a flavor of the functionality and quirks of different DBI drivers and their databases.

The line between the functionality and quirks of a given driver and the functionality and quirks of its corresponding database is rather blurred. In some cases, the database has functionality that the driver can't or doesn't access; in others, the driver may emulate functionality that the database doesn't support, such as placeholders. So when you see the terms driver or database below, take them with a pinch of salt.

Our primary goals are:

- to provide a simple overview of each driver and database.

- to help you initially select a suitable DBI driver and database for your new applications.

- to help you identify potential issues if you need to port an existing application from one driver and database combination to another.

We don't attempt to describe the drivers and databases in detail here, and we're not reproducing their documentation. We're only interested in the key features that are most commonly used or relevant to our goals. And for those features, we're just providing an outline guide, sometimes little more than signposts. Consult the database and driver documentation for full details.

With the cooperation of the driver authors, we have produced descriptions for the following drivers and databases:

DBD::ADO

  Microsoft "Active Data Objects"

DBD::CSV

  General "Comma Separated Value" ASCII files

DBD::DB2

  IBM DB2

DBD::Empress

  Empress

DBD::Informix

  Informix

DBD::Ingres

  Ingres

DBD::InterBase

  InterBase

DBD::mysql & DBD::mSQL

  MySQL and mSQL database

DBD::ODBC

  For any ODBC data source

DBD::Oracle

  Oracle

DBD::Pg

  PostgreSQL

DBD::SearchServer

  Fulcrum Search Server

DBD::Sybase

  For Sybase and Microsoft SQL Server

DBD::XBase

  For XBase files (dBase, etc.)

For each of these drivers, we've tried to cover the same range of topics in the same order.

The topics include:

- Driver summary information

- How to connect

- Supported datatypes, their ranges and functionality

- Variations in SQL dialect and default behaviors

- Advanced database features

- How to access database metadata

Reading straight through is not for the faint-hearted. We recommend dipping in on an as-needed basis.

## B.1. Acquiring the DBI and Drivers

Before you can use a DBI driver module, you obviously need to get it from somewhere and install it on your system.

If you're on a Microsoft Windows system and using the ActiveState version of Perl, then the first thing to try is their Perl Package Manager , or PPM for short. The PPM utility is installed with ActiveState Perl and greatly simplifies downloading and installing pre-compiled modules. Installing a DBI driver using PPM also automatically installs the DBI if it's not already installed. For more information refer to:

> http://www.activestate.com/PPM/

That simple solution won't work for everyone. If you're not using ActiveState Perl on Microsoft Windows, or the driver you want isn't one that they have pre-compiled for downloading via PPM, then you'll have to travel the longer road: download the source code for the driver and build it yourself. It's usually not as hard as it may sound.

The source code for DBI drivers can be downloaded from any site that is part of the Comprehensive Perl Archive Network (CPAN). Here are a few handy URLs to get you started:

> http://www.perl.com/CPAN/modules/by-module/DBD/

> http://www.perl.org/CPAN/modules/by-module/DBD/

> http://search.cpan.org/search?mode=module&query=DBD

If you've not already installed the DBI, then you'll need to do that first. Simply substituting DBI for DBD in the URLs above will take you to the source code for the DBI module.

Remember that many drivers for database systems require some database-specific client software to be installed on the machine in order to be able to build the driver. The driver documentation should explain what's needed.

#### Driver version

`DBD::ADO` version 0.03.

At the time of this writing, the `DBD::ADO` driver, and even ADO itself, are relatively new. Things are bound to change, so be sure to read the latest documentation.

#### Feature summary

Because `DBD::ADO` acts as an interface to other lower-level database drivers within Windows, much of its behavior is governed by those drivers.

>     Transactions                           Dependent on connected data source
>     Locking                                Dependent on connected data source
>     Table joins                            Dependent on connected data source
>     LONG/LOB datatypes                     Dependent on connected data source
>     Statement handle attributes available  After execute(  )
>     Placeholders                           No, not yet
>     Stored procedures                      Limited support, no parameters
>     Bind output values                     No
>     Table name letter case                 Dependent on connected data source
>     Field name letter case                 Dependent on connected data source
>     Quoting of otherwise invalid names     Dependent on connected data source
>     Case-insensitive "LIKE" operator       Dependent on connected data source
>     Server table ROW ID pseudocolumn       Dependent on connected data source
>     Positioned update/delete               No
>     Concurrent use of multiple handles     Dependent on connected data source

#### Author and contact details

The driver is maintained by Thomas Lowery and Phlip Plumlee. They can be contacted via the dbi-users mailing list.

#### Supported database versions and options

The `DBD::ADO` module requires Microsoft ADO version 2.1 or later to work reliably. Using NT with Service Pack 4 is recommended. The module is pure Perl, making use of the `Win32::OLE` module to handle the ADO requests.

The `DBD::ADO` module supports the use of SQL statements to query any data source your raw ADO supports. This can include the Jet data drivers for the various Microsoft Office file formats, any number of ODBC data drivers, or experimental data providers that expose file system folder hierarchies or Internet directory services as data sources.

Each provider system supports SQL in some format, either in a native format like MS-SQL Server's Transact SQL or as an emulation layer in the data provider, such as a Jet data driver reading an Excel spreadsheet.

Information about ADO can be found at http://www.microsoft.com/data/ado/.

#### Differences from the DBI specification

`DBD::ADO` is a very new and currently incomplete driver. It is evolving rapidly though, and since it's written in pure Perl using `Win32::OLE`, it's easy for people to enhance.

#### Connect Syntax

The `DBI->connect()` Data Source Name, or DSN, has the following format:

>     dbi:ADO:DSN

DSN must be an ODBC Data Source Name registered with the Control Panel ODBC Data Sources applet. If your DBI application runs as a service or daemon, such as a CGI script, the DSN must appear on the "System DSN" tab.

There are no driver-specific attributes for the `DBI->connect()` method. `DBD::ADO` supports an unlimited number of concurrent data source connections to one or more data sources, subject to the limitations of those data sources.

#### Datatypes

The numeric, string, date, and LONG/LOB datatypes depend on the interaction of four forces: what a Perl "scalar" supports, how the `Win32::OLE` layer translates VARIANTs into scalars, the types that VARIANT itself permits, and the types your target provider emits.

A user/programmer must research those forces in his or her relevant documentation. Rest assured that `DBD::ADO` will then transmit the type correctly.

#### Transactions, Isolation, and Locking

`DBD::ADO` reflects the capabilities of the native connection to the user. Transactions, if a provider supports them, are per connection -- all statements derived from one connection will ``see'' updates to the data that awaits a `COMMIT` statement. Other connections to that data source will not see these pending updates.

#### SQL Dialect

Because `DBD::ADO` acts as an interface to other database drivers, the following issues are governed by those drivers and the databases they connect to:

- Case-sensitivity of LIKE operator

- Table join syntax

- Table and column names

- Row ID

- Automatic key or sequence generation

- Automatic row numbering and row count limiting

For more information, refer to the documentation for the drivers and the database being used.

The `DBD::ADO` driver does not support positioned updates and deletes.

#### Parameter Binding

Parameter binding is not yet supported by `DBD::ADO`.

#### Stored Procedures

Calling stored procedures is supported by `DBD::ADO` using the ODBC style `{call procedure_name()}` syntax.

#### Table Metadata

`DBD::ADO` does not currently support the `table_info()` method. It awaits the needed slack time and/or other volunteers.

#### Driver-Specific Attributes and Methods

The ADO connection object can be accessed from database and statement handles via the `ado_conn` attribute.

The ADO RecordSet object can be accessed from statement handles via the `ado_rs` attribute.

DBD::CSV |

#### General Information

#### Driver version

`DBD::CSV` version 0.1019

#### Feature summary

>     Transactions                           No
>     Locking                                Implicit, per-statement only
>     Table joins                            No
>     LONG/LOB datatypes                     Yes, up to 4 GB
>     Statement handle attributes available  After execute(  )
>     Placeholders                           Yes, "?" style
>     Stored procedures                      No
>     Bind output values                     No
>     Table name letter case                 Sensitive, partly depends on filesystem
>     Field name letter case                 Sensitive, stored with original letter case
>     Quoting of otherwise invalid names     No
>     Case-insensitive "LIKE" operator       Yes, "CLIKE"
>     Server table ROW ID pseudocolumn       No
>     Positioned update/delete               No
>     Concurrent use of multiple handles     Unrestricted

#### Author and contact details

The driver author is Jochen Wiedmann. He can be contacted via the dbi-users mailing list.

#### Supported database versions and options

The `DBD::CSV` driver is built upon the services of several other related modules. The `Text::CSV_XS` module is used for reading and writing CSV files. The abstract generic `DBD::File` class provides the driver framework for handling flat files. That, in turn, uses the `SQL::Statement` module to parse and evaluate simple SQL statements.

It's important to note that while just about everyone thinks they know what the CSV file format is, there is actually no formal definition of the format, and there are many subtle differences.

Here's one description of a CSV file:

> http://www.whatis.com/csvfile.htm

#### Differences from the DBI specification

`DBD::CSV` does not fully parse the statement until it's executed. Thus, attributes like `$sth->{NUM_OF_FIELDS}` are not available until after `$sth->execute( )` has been called. This is valid behavior but is important to note when porting applications originally written for other drivers.

The statement handle attributes `PRECISION`, `SCALE`, and `TYPE` are not supported. Also note that many statement attributes cease to be available after fetching all the result rows or calling the `finish( )` method.

#### Connect Syntax

The `DBI->connect()` Data Source Name, or DSN, can be one of the following:

>     dbi:CSV:
>     dbi:CSV:attrs

where `attrs` is an optional semicolon-separated list of key=value pairs.

The number of database handles is limited by memory only. It is recommended to use multiple database handles for different table formats.

Commonly used attributes include:

`f_dir=directory`

  By default, files in the current directory are treated as tables. The attribute `f_dir` makes the module open files in the given directory.

`csv_eol`
`csv_sep_char`
`csv_quote_char`
`csv_escape_char`

  These attributes are used for describing the CSV file format in use. For example, to open /etc/passwd, which is colon-separated and line-feed terminated, as a table, one would use:

  >     csv_eol=\n;csv_sep_char=:

  The defaults are `\r\n`, comma (`,`), double quote ("), and double quote (") respectively. All of these attributes and defaults are inherited from the `Text::CSV_XS` module.

#### Datatypes

#### Numeric data handling

Without question, the main disadvantage of the `DBD::CSV` module is the lack of appropriate type handling. While reading a CSV table, you have no way to reliably determine the correct datatype of the fields. All fields look like strings and are treated as such by default.

The `SQL::Statement` module, and hence the `DBD::CSV` driver, accepts the numeric types `INTEGER` and `REAL` in `CREATE` `TABLE` statements, but they are always stored as strings and, by default, retrieved as strings.

It is possible to read individual columns as integers or doubles, in which case they are converted to Perl's internal datatypes IV and NV -- integer and numeric value respectively. Unsigned values are not supported.

To assign certain datatypes to columns, you have to create metadata definitions. The following example reads a table table_name with columns `I`, `N`, and `P` of type `INTEGER`, `DOUBLE`, and `STRING`, respectively:

>     my $dbh = DBI->connect("DBI:CSV:", '', '');
>     $dbh->{csv_tables}->{table_name}->{types} = [
>         Text::CSV_XS::IV(  ), Text::CSV_XS::NV(  ), Text::CSV_XS::PV(  )
>     ];
>     my $sth = $dbh->prepare("SELECT id, sales, description FROM table_name");

#### String data handling

Similar to numeric values, `DBD::CSV` accepts more datatypes in `CREATE` `TABLE` statements than it really supports. You can use `CHAR(n)` and `VARCHAR(n)` with arbitrary numbers `n`, `BLOB`, or `TEXT`, but in fact these are always `BLOB`s, in a loose kind of way.

The one underlying string type can store any binary data including embedded NUL characters. However, many other CSV tools may choke if given such data.

#### Date data handling

No date or time types are directly supported.

#### LONG/BLOB data handling

`BLOB`s are equivalent to strings. They are only limited in size by available memory.

#### Other data handling issues

The `type_info( )` method is supported.

#### Transactions, Isolation, and Locking

The driver doesn't support transactions.

No explicit locks are supported. Tables are locked while statements are executed, but the lock is immediately released once the statement is completed.

#### SQL Dialect

#### Case sensitivity of LIKE operator

Two different `LIKE` operators are supported. `LIKE` is case-sensitive, whereas `CLIKE` is not.

#### Table join syntax

Table joins are not supported.

#### Table and column names

Table and column names are case-sensitive. However, you should consider that table names are in fact filenames, so tables Foo and foo may both be present with the same data. However, they may be subject to different metadata definitions in `$dbh->{csv_tables}`.

#### Row ID

Row IDs are not supported.

#### Automatic key or sequence generation

Neither automatic keys nor sequences are supported.

#### Automatic row numbering and row count limiting

Neither automatic row numbering nor row count limitations are supported.

#### Positioned updates and deletes

Positioned updates and deletes are not supported.

#### Parameter Binding

Question marks are supported as placeholders, as in:

>     $dbh->do("INSERT INTO A VALUES (?, ?)", undef, $id, $name);

The `:1` placeholder style is not supported.

#### Stored Procedures

Stored procedures are not supported.

#### Table Metadata

By default, the driver expects the column names to be stored in the table's first row, as in:

>     login:password:uid:gid:comment:shell:homedir
>     root:s34hj34n34jh:0:0:Superuser:/bin/bash:/root

If column names are not present, you may specify column names via:

>     $dbh->{csv_tables}->{$table}->{skip_rows} = 0;
>     $dbh->{csv_tables}->{$table}->{col_names} =
>         [qw(login password uid gid comment shell homedir)];

in which case the first row is treated as a data row.

If column names are not supplied and not read from the first row, the names col0, col1, etc. are generated automatically.

Column names can be retrieved via the standard `$sth->{NAME}` attribute. The NULLABLE attribute returns an array of all ones. Other metadata attributes are not supported.

The table names, or filenames, can be read via `$dbh->table_info()` or `$dbh->tables()` as usual.

#### Driver-Specific Attributes and Methods

Besides the attributes `f_dir`, `csv_eol`, `csv_sep_char`, `csv_quote_char`, and `csv_sep_char` that have already been discussed above, the most important database handle attribute is:

>     $dbh->{csv_tables}

`csv_tables` is used for specifying table metadata. It is a hash ref with table names as keys, the values being hash refs with the following attributes:

`file`

  The filename being associated with the table. By default, the file name is `$dbh->{f_dir}/$table`.

`col_names`

  An array ref of column names.

`skip_rows`

  This number of rows will be read from the top of the file before reading the table data, and the first of those will be treated as an array of column names. However, the `col_names` attribute takes precedence.

`types`

  This is an array ref of the `Text::CSV_XS` type values for the corresponding columns. Three types are supported and their values are defined by the `IV()`, `NV()`, and `PV()` functions in the `Text::CSV_XS` package.

There are no driver-specific statement handle attributes and no private methods for either type of handle.

DBD::DB2 |

#### General Information

#### Driver version

`DBD::DB2` version 0.71

#### Feature summary

>     Transactions                           Yes
>     Locking                                Yes, implicit and explicit
>     Table joins                            Yes, inner and outer
>     LONG/LOB datatypes                     Yes, up to 2 GB
>     Statement handle attributes available  After prepare(  )
>     Placeholders                           Yes, "?" (native)
>     Stored procedures                      Yes
>     Bind output values                     No
>     Table name letter case                 Insensitive, stored as uppercase
>     Field name letter case                 Insensitive, stored as uppercase
>     Quoting of otherwise invalid names     Yes, via double quotes
>     Case-insensitive "LIKE" operator       No
>     Server table ROW ID pseudocolumn       No
>     Positioned update/delete               Yes
>     Concurrent use of multiple handles     Unrestricted

#### Author and contact details

Support for the `DBD::DB2` driver is provided by IBM through its service agreements for DB2 UDB. Any comments, suggestions, or enhancement requests can be sent via email to db2perl@ca.ibm.com. Please see the web site at:

> http://www.ibm.com/data/db2/perl

for more information.

#### Supported database versions and options

The `DBD::DB2` driver supports DB2 UDB V5.2 and later.

Here are some URLs to more database/driver-specific information:

> http://www.software.ibm.com/data/db2/perl

> http://www.software.ibm.com/data/db2

> http://www.software.ibm.com/data/db2/library

> http://www.software.ibm.com/data/db2/udb/ad

#### Differences from the DBI specification

The only significant difference in behavior from the current DBI specification is the way in which datatypes are specified in the `bind_ param( )` method. Please see the information later in this section of the document about using the `bind_ param( )` method with the `DBD::DB2` driver.

#### Connect Syntax

The `DBI->connect()` Data Source Name, or DSN, is specified as follows:

>     dbi:DB2:database_name

There are no driver-specific attributes for the `DBI->connect()` method.

`DBD::DB2` supports concurrent database connections to one or more databases.

#### Datatypes

#### Numeric data handling

DB2 UDB supports the following numeric datatypes:

>     SMALLINT
>     INTEGER
>     BIGINT
>     REAL
>     DOUBLE
>     FLOAT
>     DECIMAL or NUMERIC

A `SMALLINT` is a two-byte integer than can range from -32768 to +32767. The maximum precision is 5. Scale is not applicable.

An `INTEGER` is a four-byte integer that can range from -2147483648 to +2147483647. The maximum precision is 10. Scale is not applicable.

A `BIGINT` is an eight-byte integer that can range from -9223372036854775808 to +9223372036854775807. The maximum precision is 19. Scale is not applicable.

A `REAL` is a 32-bit approximation of a real number. The number can be or can range from -3.402e+38 to -1.175e-37, or from +1.175e-37 to +3.402e+38. The maximum precision is 7. Scale is not applicable.

A `DOUBLE` or `FLOAT` is a 64-bit approximation of a real number. The number can be or can range from -1.79769e+308 to -2.225e-307, or from 2.225e-307 to 1.79769e+308. The maximum precision is 15. Scale is not applicable.

A `DECIMAL` or `NUMERIC` value is a packed decimal number with an implicit decimal point that can range from -10**31+1 to +10**31-1. The maximum precision is 31 digits. The scale cannot be negative or greater than the precision.

Notice that DB2 supports numbers outside the typical valid range for Perl numbers. This isn't a major problem because `DBD::DB2` always returns all numbers as strings.

#### String data handling

DB2 UDB supports the following string datatypes:

>     CHAR
>     CHAR FOR BIT DATA
>     VARCHAR
>     VARCHAR FOR BIT DATA
>     GRAPHIC
>     VARGRAPHIC

`CHAR` is a fixed-length character string that can be up to 254 bytes long. `VARCHAR` is a variable-length character string that can be up to 32672 bytes. The `FOR` `BIT` `DATA` variants are used for data not associated with a particular coded character set.

`GRAPHIC` is a fixed-length string of double-byte characters that can be up to 127 characters long.

`VARGRAPHIC` is a variable-length string of double-byte characters that can be up to 16336 characters long.

The `CHAR` and `GRAPHIC` types are fixed-length strings, padded with blanks.

For DB2 UDB, `CHAR` fields can be in mixed codesets (national character sets). The non-ASCII characters are handled according to the mixed code page definition. For example, Shift-JIS characters in the range 0x81 to 0x9F and 0xE0 to 0xFC are DBCS introducer bytes, and characters in the range 0xA0 to 0xDF are single-byte Katakana characters. Blank padding for `CHAR` fields is always with ASCII blank (single-byte blank). For UTF-8, characters with the sign bit set are interpreted according to the UTF-8 definition.

`GRAPHIC` datatypes are stored as pure double-byte in the default code page of the database, or in UCS-2 in the case of a Unicode database. Blank padding for `GRAPHIC` fields is always with the DBCS blank of the corresponding code page, or with the UCS-2 blank ( U+0020 ) in the case of a Unicode database.

Code page conversions between the client code page and the database code page are automatically performed by DB2 UDB.

Unicode support is provided with DB2 UDB Version 5 + FixPak 7 (DB2 UDB V5.2 is actually DB2 UDB V5 + FixPak 6). In a Unicode database, `CHAR` datatypes are stored in UTF-8 format and `GRAPHIC` datatypes are stored in UCS-2 format.

With DB2 UDB Version 6.1, the `VARCHAR( )` function has been extended to convert graphic string datatypes to a `VARCHAR`, with the exception of `LONG` `VARGRAPHIC` and `DBCLOB`. This function is valid for UCS-2 databases only. For non-Unicode databases, this is not allowed.

All character types can store strings with embedded `nul( "\0" )` bytes.

Strings can be concatenated using the `||` operator or the `CONCAT(s1,s2)` SQL function.

#### Date data handling

DB2 UDB supports the following date, time, and date/time datatypes:

>     DATE
>     TIME
>     TIMESTAMP

`DATE` is a three-part value consisting of year, month, and day. The range of the year part is 0001 to 9999. Two-digit years cannot be used with DB2 UDB. Years must be specified with all four digits.

`TIME` is a three-part value consisting of hour, minute, and second designates a time of day under a 24-hour clock.

`TIMESTAMP` is a seven-part value, consisting of year, month, day, hour, minute, second, and microsecond, that designates a date and time as defined above, except that the time includes a fractional specification of microseconds. If you specify a `TIMESTAMP` value without a time component, the default time is 00:00:00 (midnight).

The current date, time, and date/time can be retrieved using the `CURRENT` `DATE`, `CURRENT` `TIME`, and `CURRENT` `TIMESTAMP` special registers.

DB2 UDB supports the following date, time, and date/time formats:

>     ISO   (International Standards Organization)
>     USA   (IBM USA standard)
>     EUR   (IBM European standard)
>     JIS   (Japanese Industrial Standard Christian era)
>     LOC   (site-defined, depends on database country code)

You can input date and date/time values in any supported format. For example:

>     create table datetest(dt date);
>     insert into datetest('1991-10-27');
>     insert into datetest('10/27/1991');

The default output format for `DATE`, `TIME`, and `TIMESTAMP` is that format that is associated with the country code of the database (LOC format above). You can use the `CHAR()` function and specify an alternate format.

Datetime values can be incremented, decremented, and subtracted. DB2 UDB provides a wide range of date functions including `DAY( )`, `DAYOFWEEK( )`, `DAYOFYEAR( )`, `MONTHNAME( )`, and `TIMESTAMPDIFF( )`. See the DB2 UDB documentation for additional functions.

The following SQL expression can be used to convert an integer "seconds since 1-jan-1970" value to the corresponding database date/time (local time not GMT):

>     TIMESTAMP('1970-01-01','00:00') + seconds_since_epoch

There is no simple expression that will do the reverse. Subtracting `timestamp('1970-01-01','00:00')` from another timestamp gives a timestamp duration which is a `DECIMAL(20,6)` value with format `yyyymmddhhmmss.zzzzzz`.

DB2 does no automatic time zone adjustments.

#### LONG/BLOB data handling

DB2 UDB supports the following `LONG`/`BLOB` datatypes:

>     BLOB
>     CLOB
>     DBCLOB
>     LONG VARCHAR
>     LONG VARCHAR FOR BIT DATA
>     LONG VARGRAPHIC

`BLOB` (binary large object) is a variable-length string measured in bytes that can be up to 2 GB long. A `BLOB` is primarily intended to hold non-traditional data such as pictures, voice, and mixed media. BLOBs are not associated with a coded character set (similar to `FOR` `BIT` `DATA` character strings; see below).

`CLOB` (character large object) is a variable-length string measured in bytes that can be up to 2 GB long. A `CLOB` is used to store large character-based data.

`DBCLOB` (double-byte character large object) is a variable-length string of double-byte characters that can be up to 1,073,741,823 characters long. A `DBCLOB` is used to store large DBCS character based data.

`LONG` `VARCHAR` is a variable-length character string that can be up to 32,700 bytes long. `LONG` `VARCHAR` `FOR` `BIT` `DATA` is used for data not associated with a coded character set.

`LONG` `VARGRAPHIC` is a variable-length string of double-byte characters that can be up to 16,350 characters long.

None of these types need to be passed to and from the database as pairs of hex digits.

Sadly, the `DBD::DB2` driver does not yet support the `LongReadLen` and `LongTruncOk` attributes. Values of any length can be inserted and fetched up to the maximum size of the corresponding datatype although system resources may be a constraint.

The `DBD::DB2` driver is unusual in that it requires heavy use of bind parameter attributes both for ordinary types and for `LONG`/`BLOB` types. For example, here's an attribute hash for a `CLOB`, which will have a maximum length of 100K in this particular application:

>     $attrib_clob = {
>       ParamT => SQL_PARAM_INPUT,
>       Ctype  => SQL_C_CHAR,
>       Stype  => SQL_CLOB,
>       Prec   => 100000
>       Scale  => 0,
>     };

#### Other data handling issues

The `DBD::DB2` driver does not yet support the `type_info()` method.

DB2 UDB does not automatically convert strings to numbers or numbers to strings.

#### Transactions, Isolation, and Locking

DB2 UDB supports transactions and four transaction isolation levels: Repeatable Read, Read Stability, Cursor Stability, Uncommited Read. The default transaction isolation level is Cursor Stability.

For the `DBD::DB2` driver, the isolation level can be changed by setting the TXNISOLATION keyword in the db2cli.ini file to the desired value. This keyword is set in a database-specific section, meaning that it will affect all applications that connect to that particular database. There is no way to change the isolation level from SQL.

The default behavior for reading and writing is based on the isolation level. Rows returned by a `SELECT` statement can be explicitly locked by appending `FOR UPDATE` and a list of field names to the `SELECT` statement. For example:

>     SELECT colname1, colname2
>     FROM tablename
>     WHERE colname1 = 'testvalue'
>     FOR UPDATE OF colname1, colname2

The `LOCK TABLE table_name IN lock_mode` statement can be used to apply an explicit lock on an entire table.

#### SQL Dialect

#### Case sensitivity of LIKE operator

The `LIKE` operator is case-sensitive.

#### Table join syntax

You can perform an equi-join, or inner join, using the standard `WHERE a.field =` `b.field` syntax. You can also use the following syntax:

>     SELECT tablea.col1, tableb.col1
>      FROM tablea INNER JOIN tableb
>      ON tableb.name = tablea.name

DB2 UDB supports left outer joins, right outer joins, and full outer joins. For example, to perform a left outer join, you can use the following statement:

>     SELECT tablea.col1, tablea.col2, tableb.col1, tableb.col2
>      FROM tablea LEFT OUTER JOIN tableb
>      ON tableb.name = tablea.name

Changing "LEFT" to "RIGHT" or "FULL" gives you the other forms of outer join.

#### Table and column names

In DB2 UDB Version 5.2, the maximum length of table names and column names is 18. In DB2 UDB Version 6.1, the maximum length of table names will be increased to 128 and the maximum length of column names will be increased to 30.

The first character must be a letter, but the rest can be any combination of uppercase letters, digits, and underscores.

Table and field names can be delimited by double quotes (") and can contain the same characters as described above plus lowercase letters.

Table and column names are stored as uppercase in the catalogs unless delimited. Delimited identifiers preserve the case. Two consecutive quotation marks are used to represent one quotation mark within the delimited identifier.

National characters can be used in table and column names.

#### Row ID

DB2 UDB does not support a "table row ID" pseudocolumn.

#### Automatic key or sequence generation

The `GENERATE_UNIQUE` function can be used to provide unique values (keys) in a table. For example:

>     CREATE TABLE EMP_UPDATE (
>       UNIQUE_ID CHAR(13) FOR BIT DATA,  -- note the "FOR BIT DATA"
>       EMPNO CHAR(6),
>       TEXT VARCHAR(1000)
>     )
>     INSERT INTO EMP_UPDATE VALUES
>       (GENERATE_UNIQUE(  ), '000020', 'Update entry...'),
>       (GENERATE_UNIQUE(  ), '000050', 'Update entry...')

Sadly, DB2 does not provide any way to discover the most recent value generated by `GENERATE_UNIQUE`.

DB2 UDB does not support named sequence generators.

#### Automatic row numbering and row count limiting

There is no pseudocolumn that can be used to sequentially number the rows fetched by a `SELECT` statement. However, you can number the rows of a result set using the OLAP function `ROWNUMBER`. For example:

>     SELECT ROWNUMBER(  ) OVER (order by lastname) AS number, lastname, salary
>     FROM employee ORDER BY number;

This returns the rows of the employee table with numbers assigned according to the ascending order of last names, ordered by the row numbers.

A cursor can be declared with the `FETCH FIRST n ROWS ONLY` clause to limit the number of rows returned.

#### Positioned updates and deletes

DB2 UDB supports positioned updates and deletes. Since specific testing of this functionality has not been done with the `DBD::DB2` driver, it's not officially supported; however, no problems are anticipated.

The syntax for a positioned update is as follows. `DELETE` has a similar syntax.

>     "UPDATE ... WHERE CURRENT OF $sth->{CursorName}"

#### Parameter Binding

Parameter binding is directly supported by DB2 UDB. Only the standard `?` style of placeholders is supported.

The `DBD::DB2` driver does not support the `TYPE` attribute exactly as described in the DBI documentation. Attribute hashes are used to pass type information to the `bind_ param()` method. An attribute hash is simply a collection of information about a particular type of data. (See the `DBD::DB2` documentation for a list of predefined attribute hashes).

The following is an example of how a complete new attribute hash can be created:

>     $attrib_char = {
>       ParamT => SQL_PARAM_INPUT,
>       Ctype  => SQL_C_CHAR,
>       Stype  => SQL_CHAR,
>       Prec   => 254,
>       Scale  => 0,
>     };

#### Stored Procedures

Stored procedures are invoked by using the following SQL syntax:

>     CALL procedure-name(argument, ...)

#### Table Metadata

`DBD::DB2` does not yet support the `table_info()` method.

The `SYSCAT.COLUMNS` view contains one row for each column that is defined for all tables and views in the database.

The `SYSCAT.INDEXES` view contains one row for each index that is defined for all tables in a database. Primary keys are implemented as unique indexes.

#### Driver-Specific Attributes and Methods

`DBD::DB2` has no driver-specific attributes or methods.

DBD::Empress and DBD::EmpressNet |

#### General Information

#### Driver version

`DBD::Empress` version 0.51.

#### Feature summary

>     Transactions                           Yes
>     Locking                                Yes, implicit and explicit
>     Table joins                            Yes, inner and outer
>     LONG/LOB datatypes                     Yes, up to 2 GB
>     Statement handle attributes available  After prepare(  )
>     Placeholders                           Yes, "?" (native)
>     Stored procedures                      Yes
>     Bind output values                     No
>     Table name letter case                 Sensitive, stored as defined
>     Field name letter case                 Sensitive, stored as defined
>     Quoting of otherwise invalid names     Yes, via double quotes
>     Case-insensitive "LIKE" operator       Yes, "MATCH"
>     Server table ROW ID pseudocolumn       Yes, "MS_RECORD_NUMBER"
>     Positioned update/delete               No
>     Concurrent use of multiple handles     Yes, with some restrictions

#### Author and contact details

The driver was written by Steve Williams. He can be contacted at swilliam@empress.com.

#### Supported database versions and options

`DBD::Empress` supports Empress V6.10 and later. For further information refer to:

> http://www.empress.com

These drivers use the same Perl interface but use a different underlying database interface. `DBD::Empress` is for direct access of databases, while `DBD::EmpressNet` is for distibuted database connected via the Empress Connectivity Server (referred to in Empress v8.10 and earlier as the Empress ODBC server).

#### Differences from the DBI specification

There are no significant differences.

#### Connect Syntax

The `DBI->connect()` Data Source Name, or DSN, can be one of the following:

>     dbi:Empress:physical_database
>     dbi:EmpressNet:logical_database
>     dbi:EmpressNet:SERVER=server_name;DATABASE=physical_database;PORT=port_number

There are no driver-specific attributes for the `DBI->connect()` method.

`DBD::EmpressNet` supports an unlimited number of concurrent database connections to one or more databases.

`DBD::Empress` also supports multiple concurrent database connections to one or more databases. However, these connections are simulated, and there are therefore a number of limitations. Most of these limitations are associated with transaction processing: 1) `AutoCommit` must be on or off for all connections; and 2) Switching processing from one database to another automatically commits any transactions on the first database.

#### Datatypes

#### Numeric data handling

Empress RDBMS supports the following numeric datatypes:

>     DECIMAL(p,s)      1 to 15 digits
>     DOLLAR(p,type)    1 to 13 digits
>     REAL              Typically 4-byte single precision float
>     FLOAT(p)          Typically 4 or 8-byte float as required
>     LONGFLOAT         Typically 8-byte double precision float
>     SHORTINTEGER             -127 to 127
>     INTEGER                -32767 to 32767
>     LONGINTEGER       -2147483647 to 2147483647

The DBD driver supports Empress Generic datatypes only. This means that all data for a specific group will be retrieved as the same datatype. For example, `SHORTINTEGER`, `INTEGER`, and `LONGINTEGER` will all be retrieved as `LONGINTEGER`.

`DBD::Empress` always returns all numbers as strings.

#### String data handling

Empress RDBMS supports the following string datatypes:

>     CHAR (length, type)
>     NLSCHAR (length, type)
>     TEXT (display_length, primary, overflow, extent)
>     NLSTEXT (display_length, primary, overflow, extent)

All arguments have default values. See Empress SQL Reference (A4) for details. The maximum size for all string types is typically 2**31-1 bytes (2 GB). None of the string types are blank padded.

`NLSCHAR` and `NLSTEXT` are can be used for storage of 8-bit and multibyte characters but UTF-8 is not currently supported.

Strings can be concatenated using the `s1 CONCAT(s2)` SQL function.

#### Date data handling

Empress RDBMS supports the following date/time datatypes:

>     DATE(t)            = 0000-01-01 to 9999-12-31 at 1 day resolution
>     TIME(t)            = 1970-01-01 to 2035-12-31 at 1 second resolution
>     MICROTIMESTAMP(t)  = 0000-01-01 to 9999-12-31 at 1 microsecond resolution

The `(t)` is the format type for default output. This is one of the nine types defined in the section on date/time formats.

Empress supports nine formats for date/time types:

>     Type  Date                Time                         MicroTimestamp
>     0     yyyymmdd            yyyymmddhhmmss               yyyymmddhhmmssffffff
>     1     dd aaaaaaaaa yyyy   dd aaaaaaaaa yyyy hh:mm:ss   dd aaaaaaaaa yyyy hh:mm:ss.
>                                                            fffff
>     2     aaaaaaaaa dd, yyyy  aaaaaaaaa dd, yyyy hh:mm:ss  aaaaaaaaa dd, yyyy hh:mm:
>                                                            ss.fffff
>     3     mm/dd/yy            mm/dd/yy hh:mm:ss            mm/dd/yy hh:mm:ss.ffffff
>     4     dd/mm/yy            dd/mm/yy hh:mm:ss            dd/mm/yy hh:mm:ss.ffffff
>     5     dd aaa yy           dd aaa yy hh:mm:ss           dd aaa yy hh:mm:ss.ffffff
>     6     aaa dd, yy          aaa dd, yy hh:mm:ss          aaa dd, yy hh:mm:ss.fffff
>     7     mm/dd/yyyy          mm/dd/yyyy hh:mm:ss          mm/dd/yyyy hh:mm:ss.ffffff
>     8     dd/mm/yyyy          dd/mm/yyyy hh:mm:ss          dd/mm/yyyy hh:mm:ss.ffffff

The date part for all types is not optional. If you specify a value without a time component, the default time is 00:00:00 (midnight). If only two digits of the year are input, then the century pivots on the Empress variable `MSDATELIMIT`. For Empress v8.xx and above, the default for this is 1950. Earlier versions of Empress defaulted to 1900.

Empress accepts any of the nine specified types as input. The only limitation is that you cannot insert a four-digit year into a date type that uses a two-digit format. It always uses `MSDATELIMIT` for input dates.

For output, `DBD::Empress` uses just `yyyymmddhhmmssffffff` and `DBD:: EmpressNet` uses just `yyyy-mm-dd hh:mm:ss.ffffff`. Empress does not support changing of the default display formats. It is not possible to format a date/time value in other styles for output. The best approach is to select the components of the date/time, using SQL functions like `DAYOF(d)` and `MONTHOF(d)`, and format them using Perl.

The current date/time at the server, can be obtained using the `NOW` or `TODAY` pseudo constants. `NOW` returns the current date and time. `TODAY` returns the date portion only.

Date and time arithmetic can be done using the Empress date/time operators. For example:

>     NOW + 2 MINUTES + 5 SECONDS
>     TODAY - 3 DAYS

Empress provides a wide range of date functions including `DAYOF( )`, `MONTHOF( )`, `YEAROF( )`, `HOUROF( )`, `MINUTEOF( )`, `SECONDOF( )`, `WEEKOFYEAR( )`, `DAYNAME( )`, `DAYOFWEEK( )`, `DAYOFYEAR( )`, and `DATENEXT( )`.

The following SQL expression:

>     '1 jan 1970' + unix_time_field SECONDS

would convert to a local time from 1 Jan 1970, but the GMT base cannot be generated directly.

The number of seconds since 1 Jan 1970 for date granularity can be obtained for the local time zone (not GMT) using:

>     (date_field - '1 jan 1970') * 86400

Empress does no automatic time zone adjustments.

#### LONG/BLOB data handling

Empress RDBMS supports the following `LONG` datatypes:

>     TEXT      Variable length 7-bit character data
>     NLSTEXT   As TEXT but allows 8-bit characters
>     BULK      User Interpreted (Byte Stream)

The maximum size for all these types is typically 2**31-1 bytes (2 GB).

`LongReadLen` works as defined for `DBD::EmpressNet` but is ignored for `DBD:: Empress`. The maximum `LongReadLen` is limited to 2 GB typically. `LongTruncOk` is not implemented.

No special handling is required for binding LONG/BLOB datatypes. The `TYPE` attribute is currently not used when binding parameters. The maximum length of `bind_ param( )` parameters is limited by the capabilities of the OS or the size of the C `int`, whichever comes first.

#### Other data handling issues

The `type_info( )` method is not supported.

Empress automatically converts strings to numbers and dates, and numbers and dates to strings, as needed.

#### Transactions, Isolation, and Locking

`DBD::Empress` supports transactions. The default isolation level is Serializable.

Other transaction isolation levels are not explicitly supported. However Read Uncommited is supported on a single query basis. This is activated by adding the `BYPASS` option into each SQL statement.

For example:

>     SELECT BYPASS * FROM table_name

Record level locking is the default. Read locks do not block other read locks, but read locks block write locks, and write locks block all other locks. Write locks can be bypassed for read using the `BYPASS` option.

When in transaction mode (`AutoCommit` off), selected rows are automatically locked against update unless the `BYPASS` option is used in the `SELECT` statement.

The `LOCK TABLE table_name IN lock_mode` statement can be used to apply an explicit lock on a table. Lock mode can be `EXCLUSIVE` or `SHARE`. `SHARE` requires the user to have `SELECT` or `UPDATE` privileges on the table. `EXCLUSIVE` requires the user to have `UPDATE`, `INSERT`, or `DELETE` privileges.

#### SQL Dialect

#### Case sensitivity of LIKE operator

The `LIKE` operator is case-sensitive. The `MATCH` operator is case-insensitive.

#### Table join syntax

For outer joins, the Empress keyword `OUTER` should be placed before the table(s) that should drive the outer join. For example:

>     SELECT customer_name, order_date
>     FROM OUTER customers, orders
>     WHERE customers.cust_id = orders.cust_id;

This returns all the rows in the customer's table that have no matching rows in the orders table. Empress returns `NULL` for any select list expressions containing columns from the orders table.

#### Table and Column Names

The names of Empress identifiers, such as tables and columns, cannot exceed 32 characters in length. The first character must be a letter, but the rest can be any combination of letters, numerals, and underscores ( _ ). Empress table/column names are stored as defined. They are case-sensitive.

Empress tables and fields can contain most ASCII characters (except `$` and `?`) if they are quoted.

Any ISO-Latin characters can be used in the base product. Specific products for other languages, such as Japanese, can handle those character sets.

#### Row ID

A table row identifier can be referenced as `MS_RECORD_NUMBER`. It can be treated as a string during fetch; however, it must be treated as an integer when it is used in a WHERE clause. It is useful only for explicit fetch; inequalities are not allowed.

>     SELECT * FROM table_name WHERE MS_RECORD_NUMBER = ?

#### Automatic key or sequence generation

Empress has no "auto increment" or "system generated" key mechanism, and does not support sequence generators.

#### Automatic row numbering and row count limiting

Neither automatic row numbering nor row count limitations are supported.

#### Positioned updates and deletes

Positioned updates and deletes are not supported.

#### Parameter Binding

Parameter binding is directly supported by Empress. Only the standard `?` style of placeholders is supported.

`DBD::Empress` recognizes the `bind_ param()` `TYPE` attribute `SQL_BINARY`. All other types are automatically bound correctly without `TYPE` being used. Unsupported types are ignored without warning.

#### Stored Procedures

`DBD::Empress` does not explicitly support stored procedures. Implicit support is available for stored procedures in SQL statements. For example:

>     $sth->prepare("SELECT func(attribute) FROM table_name");

#### Table Metadata

`DBD::Empress` does not support the `table_info()` method.

The `SYS_ATTRS` and `SYS_TABLES` system tables can be used to obtain detailed information about the columns of a table. For example:

>     SELECT * FROM sys_attrs
>     WHERE attr_tabnum = (SELECT tab_number FROM sys_tables WHERE tab_name='x')

However, this requires `SELECT` privileges on these system tables.

Detailed information about indices or keys cannot currently be easily retrieved though `DBD::Empress`. It is possible, though difficult, to interpret the contents of the system tables to obtain this information.

#### Driver-Specific Attributes and Methods

`DBD::Empress` has no significant driver-specific handle attributes or private methods.

DBD::Informix |

#### General Information

#### Driver version

`DBD::Informix` version 0.62.

#### Feature summary

>     Transactions                           Yes, if enabled when database was created
>     Locking                                Yes, implicit and explicit
>     Table joins                            Yes, inner and outer
>     LONG/LOB datatypes                     Yes, up to 2 GB
>     Statement handle attributes available  After prepare(  )
>     Placeholders                           Yes, "?" (native)
>     Stored procedures                      Yes
>     Bind output values                     Yes
>     Table name letter case                 Configurable
>     Field name letter case                 Configurable
>     Quoting of otherwise invalid names     Yes, via double quotes
>     Case-insensitive "LIKE" operator       No
>     Server table ROW ID pseudocolumn       Yes, "ROWID"
>     Positioned update/delete               Yes
>     Concurrent use of multiple handles     Unrestricted

#### Author and contact details

The driver author is Jonathan Leffler. He can be contacted via the dbi-users mailing list.

#### Supported database versions and options

The `DBD::Informix` module supports Informix OnLine and SE from version 5.00 onwards. There are some restrictions in the support for IUS (a.k.a., IDS/UDO). It uses Informix-ESQL/C (a.k.a., Informix ClientSDK). You must have a development license for Informix-ESQL/C (or the C-code version of Informix-4GL) to be able to compile the `DBD::Informix` code.

For more information, refer to:

> http://www.informix.com

> http://www.iiug.org

#### Differences from the DBI Specification

If you change `AutoCommit` after preparing a statement, you will probably run into problems that you don't expect. So don't do that.

See the `DBD::Informix` documentation for more details on this and other assorted subtle compatibility issues.

#### Connect Syntax

The `DBI->connect()` Data Source Name, or DSN, has the following form:

>     dbi:Informix:connect_string

where `connect_string` is any valid string that can be passed to the Informix `CONNECT` statement (or to the `DATABASE` statement for version 5.x systems). The acceptable notations include:

>     dbase
>     dbase@server
>     @server
>     /path/to/dbase
>     //machine/path/to/dbase

There are no driver-specific attributes for the `DBI->connect()` method.

If you're using version 6.00 or later of ESQL/C, then the number of database handles is limited only by your imagination and the computer's physical constraints. If you're using 5.x, you're stuck with one connection at a time.

#### Datatypes

#### Numeric data handling

Informix supports these numeric datatypes:

>     INTEGER           - signed 32-bit integer, excluding -2**31
>     SERIAL            - synonym for INTEGER as far as scale is concerned
>     SMALLINT          - signed 16-bit integer, excluding -2**15
>     FLOAT             - Native C 'double'
>     SMALLFLOAT        - Native C 'float'
>     REAL              - Synonym for SMALLFLOAT
>     DOUBLE PRECISION  - Synonym for FLOAT
>     DECIMAL(s)        - s-digit floating point number (non-ANSI databases)
>     DECIMAL(s)        - s-digit integer (MODE ANSI databases)
>     DECIMAL(s,p)      - s-digit fixed-point number with p decimal places
>     MONEY(s)          - s-digit fixed-point number with 2 decimal places
>     MONEY(s,p)        - s-digit fixed-point number with p decimal places
>     NUMERIC(s)        - synonym for DECIMAL(s)
>     NUMERIC(s,p)      - synonym for DECIMAL(s,p)
>     INT8              - signed 64-bit integer, excluding -2**63 (IDS/UDO)
>     SERIAL8           - synonym for INT8 as far as scale is concerned

`DBD::Informix` always returns all numbers as strings. Thus the driver puts no restriction on size of `PRECISION` or `SCALE`.

#### String data handling

Informix supports the following string datatypes:

>     VARCHAR(size)
>     NVARCHAR(size)
>     CHAR
>     CHAR(size)
>     NCHAR
>     NCHAR(size)
>     CHARACTER VARYING(size)
>     NATIONAL CHARACTER VARYING(size)
>     NATIONAL CHARACTER(size)
>     CHARACTER(size)
>     VARCHAR(size,min)   -- and synonyms for this type
>     NVARCHAR(size,min)  -- and synonyms for this type
>     LVARCHAR            -- IDS/UDO only

Arguably, `TEXT` and `BYTE` blobs should also be listed here, as they are automatically converted from/to strings.

`CHAR` types have a limit of 32767 bytes in OnLine and IDS and a slightly smaller value (325xx) for SE. For `VARCHAR` types, the limit is 255. `LVARCHAR` columns are limited to 2 KB; when used to transfer other datatypes, up to 32 KB. `DBD::Informix` 0.61 doesn't have fully operational `LVARCHAR` support.

The `CHAR` and `NCHAR` types are fixed-length and blank-padded.

Handling of national character sets depends on the database version (and is different for versions 5, for versions 6 and 7.1x, and for versions 7.2x and later). Details for version 8.x vary depending on x. It depends on the locale, determined by a wide range of standard (e.g., `LANG`, `LC_COLLATE`) and non-standard (e.g., `DBNLS`, `CLIENT_LOCALE`) environment variables. For details, read the relevant manual. Unicode is not currently directly supported by Informix (as of 1999-02-28).

Strings can be concatenated using the `||` operator.

#### Date data handling

There are two basic date/time handling types: `DATE` and `DATETIME`. `DATE` supports dates in the range 01/01/0001 through 31/12/9999. It is fairly flexible in its input and output formats. Internally, it is represented by the number of days since December 31 1899, so January 1 1900 was day 1. It does not understand the calendric gyrations of 1752, 1582-4, or the early parts of the first millenium, and imposes the calendar as of 1970-01-01 on these earlier times.

`DATETIME` has to be qualified by two components from the set:

>     YEAR MONTH DAY HOUR MINUTE SECOND FRACTION FRACTION(n) for n = 1..5

These store a date using ISO 8601 format for the constants. For example, `DATE("29/02/2000")` is equivalent to:

>     DATETIME("2000-02-29") YEAR TO DAY,

and The Epoch for POSIX systems can be expressed as:

>     DATETIME(1970-01-01 00:00:00) YEAR TO SECOND

There is no direct support for time zones.

The default date/time format depends on the environment locale settings and the version and the datatype. The `DATETIME` types are rigidly ISO 8601 except for converting one-digit or two-digit years to a four-digit equivalent, subject to version and environment.

Handling of two-digit years depends on the version, the bugs fixed, and the environment. In general terms (for current software), if the environment variable `DBCENTURY` is unset or is set to '`R`', then the current century is used. If `DBCENTURY` is '`F`', the date will be in the future; if `DBCENTURY` is '`P`', it will be in the past; if `DBCENTURY` is '`C`', it will be the closest date (50-year window, based on current day, month and year, with the time of day untested).

The current datetime is returned by the `CURRENT` function, usually qualified as `CURRENT` `YEAR` `TO` `SECOND`.

Informix provides no simple way to input or output dates and times in other formats. Whole chapters can be written on this subject.

Informix supports a draft version of the SQL2 `INTERVAL` datatype:

>     INTERVAL start[(p1)] [TO end[(p2)]]

(Where `[]` indicates optional parts.)

The following interval qualifications are possible:

>     YEAR, YEAR TO MONTH,
>     MONTH,
>     DAY, DAY TO HOUR, DAY TO MINUTE, DAY TO SECOND,
>     HOUR, HOUR TO MINUTE, HOUR TO SECOND,
>     MINUTE, MINUTE TO SECOND,
>     SECOND, FRACTION

`p1` specifies the number of digits specified in the most significant unit of the value, with a maximum of 9 and a default of 2 (except `YEAR` that defaults to 4). `p2` specifies the number of digits in fractional seconds, with a maximum of 5 and a default of 3.

Literal interval values may be specified using the following syntax:

>     INTERVAL value start[(p1)] [TO end[(p2)]]

For example:

>     INTERVAL(2) DAY
>     INTERVAL(02:03) HOUR TO MINUTE
>     INTERVAL(12345:67.891) MINUTE(5) TO FRACTION(3)

The expression "2 UNITS DAY" is equivalent to the first of these, and similar expressions can be used for any of the basic types.

A full range of operations can be performed on dates and intervals, e.g., datetime-datetime=interval, datetime+interval=datetime, interval/number=interval.

The following SQL expression can be used to convert an integer "seconds since 1-jan-1970 GMT" value to the corresponding database date/time:

>     DATETIME(1970-01-01 00:00:00) YEAR TO SECOND + seconds_since_epoch UNITS SECOND

There is no simple expression for inline use that will do the reverse. Use a stored procedure; see the comp.databases.informix archives at DejaNews, or the Informix International Users Group (IIUG) web site at http://www.iiug.org.

Informix does not handle multiple time zones in a simple manner.

#### LONG/BLOB data handling

Informix supports the following large object types:

>     BYTE  - binary data     max 2 GB
>     TEXT  - text data       max 2 GB
>     BLOB  - binary data     max 2 GB (maybe bigger); IDS/UDO only
>     CLOB  - character data  max 2 GB (maybe bigger); IDS/UDO only

`DBD::Informix` does not currently have support for `BLOB` and `CLOB` datatypes, but does support the `BYTE` and `TEXT` types.

The DBI `LongReadLen` and `LongTruncOk` attributes are not implemented. If the data selected is a `BYTE` or `TEXT` type, then the data is stored in the relevant Perl variable, unconstrained by anything except memory up to a limit of 2 GB.

The maximum length of `bind_ param( )` parameter value that can be used to insert `BYTE` or `TEXT` data is 2 GB. No specialized treatment is necessary for fetch or insert. `UPDATE` simply doesn't work.

The `bind_ param( )` method doesn't pay attention to the `TYPE` attribute. Instead, the string presented will be converted automatically to the required type. If it isn't a string type, it needs to be convertible by whichever bit of the system ends up doing the conversion. `UPDATE` can't be used with these types in `DBD::Informix`; only version 7.30 IDS provides the data necessary to be able to handle blobs.

#### Other data handling issues

The `type_info( )` method is not supported.

Non-BLOB types can be automatically converted to and from strings most of the time. Informix also supports automatic conversions between pure numeric datatypes whereever it is reasonable. Converting from `DATETIME` or `INTERVAL` to numeric datatypes is not automatic.

#### Transactions, Isolation, and Locking

Informix databases can be created with or without transaction support.

Informix supports several transaction isolation levels: REPEATABLE READ, CURSOR STABILITY, COMMITTED READ, and DIRTY READ. Refer to the Informix documentation for their exact meaning. Isolation levels apply only to ONLINE and IDS and relatives; SE supports only a level somewhere in between COMMITTED READ and DIRTY READ.

The default isolation level depends on the type of database to which you're connected. You can use `SET ISOLATION TO` level to change the isolation level. If the database is unlogged (that is, it has no transaction support), you can't set the isolation level. In some more recent versions, you can also set a transaction to `READ ONLY`.

The default locking behavior for reading and writing depends on the isolation level, the way the table was defined, and on whether or not the database was created with transactions enabled.

Rows returned by a `SELECT` statement can be locked to prevent them being changed by another transaction, by appending `FOR UPDATE` to the select statement. Optionally, you can specify a column list in parentheses after the `FOR UPDATE` clause.

The `LOCK TABLE table_name IN lock_mode` statement can be used to apply an explicit lock on a table. The lock mode can be `SHARED` or `EXCLUSIVE`. There are constraints on when tables can be unlocked, and when locks can be applied. Row/page locking occurs with cursors `FOR UPDATE`. In some types of database, some cursors are implicitly created `FOR UPDATE`.

#### SQL Dialect

#### Case sensitivity of LIKE operator

The `LIKE` operator is case-sensitive.

#### Table join syntax

All Informix versions support the basic `WHERE` `a.field` `=` `b.field` style join notation. Support for SQL-92 join notation depends on DBMS version; most do not.

Outer joins are supported. The basic version is:

>     SELECT * FROM A, OUTER B WHERE a.col1 = b.col2

All rows from `A` will be selected. Where there is one or more rows in `B` matching the row in `A` according to the join condition, the corresponding rows will be returned. Where there is no matching row in `B`, NULL will be returned in the B-columns in the `SELECT` list. There are all sorts of other contortions, such as complications with criteria in the `WHERE` clause, or nested outer joins.

#### Table and column names

For most versions, the maximum size of a table name or column name is 18 characters, as required by SQL-86. For the latest versions (Centaur, provisionally 9.2 or 7.4), the answer will be 128, as required by SQL-92. Owner (schema) names can be eight characters in the older versions and 32 in the versions with long table/column names.

The first character must be a letter, but the rest can be any combination of letters, numerals, and underscores ( `_` ).

If the `DELIMIDENT` environment variable is set, then table and column and owner names can be quoted inside double quotes, and any characters become valid. To embed a double quote in the name, use two adjacent double quotes, such as `"I said,` `""Don't"""`. (Normally, Informix is very relaxed about treating double quotes and single quotes as equivalent, so often you could write `'I` `said, "Don''t"'` as the equivalent of the previous example. With `DELIMIDENT` set, you have to be more careful.) Owner names are delimited identifiers and should be embedded in double quotes for maximum safety.

The case-preserving and case-sensitive behavior of table and column names depends on the environment and the quoting mechanisms used.

Support for using national character sets in names depends on the version and the environment (locale).

#### Row ID

Most tables have a virtual ROWID column which can be selected. Fragmented tables do not have one unless it is specified in the `WITH ROWIDS` clause when the table is created or altered. In that case, it is a physical ROWID column that otherwise appears as a virtual column (meaning `SELECT *` does not select it).

As with any type except the `BLOB` types, a ROWID can be converted to a string and used as such. Note that ROWIDs need not be contiguous, nor start at either zero or one.

#### Automatic key or sequence generation

The SERIAL and SERIAL8 datatypes are "auto incrementing" keys. If you insert a zero into these columns, the next previously unused key number is unrollbackably allocated to that row. Note that NULL can't be used; you have to insert a zero. If you insert a non-zero value into the column, the specified value is used instead. Usually, there is a unique constraint on the column to prevent duplicate entries.

To get the value just inserted, you can use:

>     $sth->{ix_sqlerrd}[1]

Informix doesn't support sequence generators directly, but you can create your own with stored procedures.

#### Automatic row numbering and row count limiting

Informix does not support a way to automatically number returned rows.

Some recent versions of Informix support a `FIRST` row count limiting directive on SELECT statements:

>     SELECT FIRST num_of_rows ...

#### Positioned updates and deletes

Positioned updates and deletes are supported using the `WHERE CURRENT OF` syntax. For example:

>     $dbh->do("UPDATE ... WHERE CURRENT OF $sth->{CursorName}");

#### Parameter Binding

Parameter binding is directly supported by Informix. Only the standard `?` style of placeholder is supported.

The `TYPE` attribute to `bind_ param()` is not currently supported, but some support is expected in a future release.

#### Stored Procedures

Some stored procedures can be used as functions in ordinary SQL:

>     SELECT proc1(Col1) FROM SomeTable WHERE Col2 = proc2(Col3);

All stored procedures can be executed via the SQL `EXECUTE PROCEDURE` statement. If the procedure returns no values, it can just be executed. If the procedure does return values, even single values via a `RETURN` statement, then it can be treated like a `SELECT` statement. So after calling `execute()` you can fetch results from the statement handle as if a `SELECT` statement had been executed. For example:

>     $sth = $dbh->prepare("EXECUTE PROCEDURE CursoryProcedure(?,?)");
>     $sth->execute(1, 12);
>     $ref = $sth->fetchall_arrayref();

#### Table Metadata

The DBI `table_info()` method isn't currently supported. The private `_tables()` method can be used to get a list of all tables or a subset.

Details of the columns of a table can be fetched using the private `_columns()` method.

The keys/indexes of a table can be fetched by querying the system catalog.

Further information about these and other issues can be found via the comp.databases.informix newsgroup, and via the International Informix User Group (IIUG) at http://www.iiug.org.

#### Driver-Specific Attributes and Methods

Refer to the `DBD::Informix` documentation for details of driver-specific database and statement handle attributes.

Private `_tables()` and `_columns()` methods give easy access to table and column details.

#### Other Significant Database or Driver Features

Temporary tables can be created during a database session that are automatically dropped at the end of that session if they have not already been dropped explicitly. It's a very handy feature.

The latest versions of Informix (IDS/UDO, IUS) support user-defined routines and user-defined types, which can be implemented in the server in C or (shortly) Java.

The SQL-92 "CASE WHEN" syntax is supported by some versions of the Informix servers. That greatly simplifies some kinds of queries.

DBD::Ingres |

#### General Information

#### Driver version

`DBD::Ingres` version 0.16 and, where noted, the 0.20 release

#### Feature summary

>     Transactions                           Yes
>     Locking                                Yes, implicit and explicit
>     Table joins                            Yes, inner and outer
>     LONG/LOB datatypes                     Yes, up to 2 GB
>     Statement handle attributes available  After prepare(  )
>     Placeholders                           Yes, "?" and ":1" styles (native)
>     Stored procedures                      Yes
>     Bind output values                     Yes
>     Table name letter case                 Insensitive, stored as uppercase
>     Field name letter case                 Insensitive, stored as uppercase
>     Quoting of otherwise invalid names     Yes, via double quotes
>     Case-insensitive "LIKE" operator       No
>     Server table ROW ID pseudocolumn       Yes, "tid"
>     Positioned update/delete               No
>     Concurrent use of multiple handles     Unrestricted

#### Author and contact details

The driver author is Henrik Tougaard. He can be contacted via the dbi-users mailing list.

#### Supported database versions and options

The `DBD::Ingres` module supports both Ingres 6.4 and OpenIngres (1.x & II). For more information about Ingres, refer to:

> http://www.cai.com/products/ingres.htm

#### Differences from the DBI specification

Prepared statements do not work across transactions because commit/rollback and close/invalidate are all prepared statements. Work is underway to fix this deficiency.

#### Connect Syntax

The `DBI->connect()` Data Source Name, or DSN, can be one of the following:

>     dbi:Ingres:dbname
>     dbi:Ingres:vnode::dbname
>     dbi:Ingres:dbname;options

Where `options` are the SQL option flags as defined in the CA-OpenIngres System Reference Guide.

There are no driver-specific attributes for the `DBI->connect()` method.

`DBD::Ingres` supports an unlimited number of concurrent database connections to one or more databases.

#### Datatypes

#### Numeric data handling

The database and driver supports one-byte, two-byte and four-byte `INTEGER`s, four-byte and eight-byte `FLOATS`, and a currency type. The database and the driver (from version 0.20) supports the `DECIMAL`-number type.

Type | Description | Range
--- | --- | ---
> INTEGER1 |
>     1-byte integer
| -128 to +127
> SMALLINT |
>     2-byte integer
| -32,678 to +32,767
> INTEGER |
>     4-byte integer
| -2,147,483,648 to +2,147,483,647
> FLOAT4 |
>     4-byte floating
| -1.0e+38 to 1.0e+38 (7 digits)
> FLOAT |
>     8-byte floating
| -1.0e+38 to 1.0e+38 (16 digits)
> MONEY |
>     8-byte money
| $-999,999,999,999.99 to $999,999,999,999.99
> DECIMAL |
>     fixed-point numeric
| Depends on precision (max 31) and scale

`DBD::Ingres` always returns all numbers as Perl numbers -- integers where possible, floating point otherwise. It is therefore possible that some precision may be lost when fetching `DECIMAL` types with a precision greater than Perl numbers (usually 16). If that's an issue, then convert the value to a string in the `SELECT` expression.

#### String data handling

Ingres and `DBD::Ingres` supports the following string datatypes:

>     VARCHAR(size)
>     CHAR(size)
>     TEXT(size)
>     C(size)

All string types have a limit of 2000 bytes. The `CHAR`, `TEXT`, and `C` types are fixed length and blank padded.

All string types can handle national character sets. The `C` type will accept only printing characters. `CHAR` and `VARCHAR` accept all character values including embedded `nul` characters ( `"\0"` ). Unicode is not formally supported yet.

Strings can be concatenated using the SQL `+` operator.

#### Date data handling

Ingres has just one date datatype: `DATE`. However, it can contain either an absolute date and time or a time interval. Dates and times are in second resolution between approximately 1-JAN-1581 and 31-DEC-2382. Intervals are stored to a one second resolution.

Ingres supports a variety of date formats, depending on the setting of the environment variable `II_DATE_FORMAT`. The default output format is `US:` `DD-MMM-YYYY` `HH:MM:SS`.

Many input formats are allowed. For the default format the following are accepted: `MM/DD/YYYY`, `DD-MMM-YYYY`, `MM-DD-YYYY`, `YYYY.MM.DD`, `YYYY_MM_DD`, `MMDDYY`, `MM-DD`, and `MM/DD`.

If you specify a `DATE` value without a time component, the default time is 00:00:00 (midnight). If you specify a `DATE` value without a date, the default date is the first day of the current month. If a date format that has a two-digit year, such as the `YY` in `DD-MON-YY` (a common default), then the date returned is always in the current century.

The following date-related functions are supported:

>     DATE(string)              - converts a string to a date
>     DATE_TRUNC(unit, date)    - date value truncated to the specified unit
>     DATE_PART(unit, date)     - integer containing the specified part
>     DATE_GMT(date)            - converts date to string "YYYY_MM_DD HH:MM:SS GMT"
>     INTERVAL(unit, interval)  - express interval as numeric count of units

The current date and time is returned by the `DATE('now')` function. The current date is returned by `DATE('today')`.

The following SQL expression can be used to convert an integer "seconds since 1-jan-1970 GMT" value to the corresponding database date/time:

>     DATE('01.01.1970 00:00 GMT')+DATE(CHAR(seconds_since_epoch)+' seconds')

And to do the reverse:

>     INT4(INTERVAL('seconds', DATE('now')-DATE('01.01.1970 00:00 GMT')))

A three-letter time zone name (from a limited set) can be appended to a date. If no time zone name is given, then the current client time zone is assumed. All datetimes are stored in the database as GMT and are converted back to the local time of the client fetching the data. All date comparisions in the server are done in GMT.

#### LONG/BLOB data handling

Ingres supports these `LONG` types:

>     LONG VARCHAR  - Character data of variable length up to 2 GB
>     LONG BYTE     - Raw binary data of variable length up to 2 GB

However, the `DBD::Ingres` driver does not yet support these types.

#### Other data handling issues

The `DBD::Ingres` driver supports the `type_info( )` method.

Ingres supports automatic conversions between datatypes wherever it's reasonable.

#### Transactions, Isolation, and Locking

Ingres supports transactions. The default transaction isolation level is Serializable. OpenIngres II supports Repeatable Read, Read Commited, and Serializable.

The reading of a record sets a read-lock preventing writers from changing that record and, depending on lock granularity, possibly other records. Other readers are not hindered in their reading. Writing a record sets a lock that prevents other writers from writing, and readers from reading.

The `SET LOCKMODE` statement allows you to change the locking granularity. It can be set to:

>     ROW    - lock only the affected rows (OpenIngres II only)
>     PAGE   - lock the page that contains the affected row
>     TABLE  - lock the entire table

With the statement `SET LOCKMODE SESSION WHERE READLOCK=NOLOCK` it is possible, but definitely not recommended, to set the isolation level to Read Uncommited.

#### SQL Dialect

#### Case sensitivity of LIKE operator

The `LIKE` operator is case-sensitive.

#### Table join syntax

OpenIngres supports outer joins in ANSI SQL-92 syntax. Ingres 6.4 does not support outer joins.

#### Table and column names

The names of identifiers cannot exceed 32 characters. The first character must be a letter or an underscore ( `_`), but the rest can be any combination of letters, numerals, dollar signs (`$`), pound signs (`#`), and at signs ( `@`).

However, if an identifier is enclosed by double quotes ("), it can contain any combination of legal characters, including spaces but excluding quotation marks. This is not supported in Ingres 6.4.

Case significance is determined by the settings for the Ingres installation as set by the administrator when Ingres is installed.

National character sets can be used in identifiers, if enclosed in double quotes.

#### Row ID

The Ingres "row ID" pseudocolumn is called `tid`. It's an integer. It can be used without special handling. For example:

>     SELECT * FROM table WHERE tid=1029;

#### Automatic key or sequence generation

OpenIngres II supports "logical key" columns. They are defined by using a special datatype: `TABLE_KEY WITH SYSTEM MAINTAINED`. Ingres 6.4 required an extra-cost option to support that feature.

A column can be defined as either `TABLE_KEY` or `OBJECT_KEY`. Table_keys are unique in the table, whereas object_keys are unique in the entire database.

`DBD::Ingres` can't currently find the value of the last automatic key inserted, though it may do so in the future if enough people ask nicely, or someone contributes the code.

#### Automatic row numbering and row count limiting

Neither automatic row numbering nor row count limitations are supported.

#### Positioned updates and deletes

Positioned updates and deletes are supported in `DBD::Ingres` version 0.20 using the `WHERE CURRENT OF` syntax. For example:

>     $dbh->do("UPDATE ... WHERE CURRENT OF $sth->{CursorName}");

The `CursorName` is automatically defined by `DBD::Ingres` for each prepared statement.

#### Parameter Binding

Parameter binding is directly supported by Ingres. Only the standard `?` placeholder style is supported.

When using the `bind_ param()` method, the common integer, float, and char types can be defined using the `TYPE` attribute. Unsupported values of the `TYPE` attribute generate a warning.

#### Stored Procedures

Calling a stored procedure is done by the `execute procedure` statement. For example:

>     $dbh->do("execute procedure my_proc(param1='value')");

It is not yet possible to get results.

#### Table Metadata

`DBD::Ingres` version 0.20 supports the `table_info()` method.

The `IICOLUMNS` catalog contains information about all columns of a table.

The `IIINDEXES` catalog contains detailed information about all indexes in the database, one row per index. The `IIINDEX_COLUMNS` catalog contains information about the columns that make up each index.

Primary keys are indicated in the `key_sequence` field of the `IICOLUMNS` catalog.

#### Driver-Specific Attributes and Methods

`DBD::Ingres` has no driver-specific database handle attributes. However, it does support a number of statement handle attributes. Each returns a reference to an array of values, one for each column of the select results. These attributes are:

`ing_type`

  '`i`' for integer columns, '`f`' for float, and '`s`' for strings

`ing_ingtype`

  The numeric Ingres type of the columns

`ing_length`

  The Ingres length of the columns (as used in the database)

`DBD::Ingres` supports just one private method:

`get_dbevent()`

  This private method calls `GET` `DBEVENT` and `INQUIRE_INGRES` to fetch a pending database event. If called without an argument, a blocking `GET` `DBEVENT WITH WAIT` is called. A numeric argument results in a call to `GET` `DBEVENT WITH WAIT= :seconds`.

DBD::InterBase |

#### General Information

#### Driver version

`DBD::InterBase` version 0.021

This version of the `DBD::InterBase` driver is a pure Perl wrapper module around the `IBPerl` module. The author is working on a direct XS version, so be sure to read the latest documentation.

#### Feature summary

>     Transactions                           Yes
>     Locking                                Yes, implicit and explicit
>     Table joins                            Yes, inner and outer
>     LONG/LOB datatypes                     Yes, up to 4 GB
>     Statement handle attributes available  After first row fetched
>     Placeholders                           Yes, "?" style (native)
>     Stored procedures                      Yes
>     Bind output values                     Yes
>     Table name letter case                 Insensitive, stored as uppercase
>     Field name letter case                 Insensitive, stored as uppercase
>     Quoting of otherwise invalid names     Yes, via double quotes
>     Case-insensitive "LIKE" operator       No
>     Server table ROW ID pseudocolumn       No
>     Positioned update/delete               No
>     Concurrent use of multiple handles     Unrestricted

#### Author and contact details

The driver author is Edwin Pratomo. He can be contacted via the dbi-users mailing list, or at ed.pratomo@computer.org.

#### Supported database versions and options

`DBD::InterBase` has been used to access InterBase 4.0 for Linux, and InterBase 5.5 for NT, and should also work with any version of InterBase above version 3.3 supported by `IBPerl`. `DBD::InterBase` also inherits all limitations applied to `IBPerl` 0.7, for instance, lack of metadata.

For further information about InterBase, refer to:

> http://www.interbase.com

> http://www.interbase.com/products/dsqlsyntax.html

#### Differences from the DBI specification

`DBD::InterBase` does not have access to statement metadata until after the statement has been executed and the first row fetched. Thus, attributes like `$sth->{NUM_OF_FIELDS}` are not available until after `$sth->execute( )` and a fetch method has been called. Hopefully this will be fixed in a later version.

#### Connect Syntax

The `DBI->connect()` Data Source Name, or DSN, has the following format:

>     dbi:InterBase:attrs

where `attrs` is a semicolon-separated list of key=value pairs Valid attributes include:

database

  Specifies the full path to the database within the server that should be made the default database.

host (optional)

  Specify the host name of the InterBase server to connect to. Default to localhost.

role (optional)

  Specify an SQL role name -- supported only in InterBase 5.0 and later.

charset (optional)

  Specify the client character set to use. Useful if the client's default character set is different from the server. Using this will enable automatic character conversion from one character set to the other. Default to `NONE`.

`DBD::InterBase` supports an unlimited number of concurrent database connections to one or more databases.

#### Datatypes

#### Numeric data handling

InterBase supports `INTEGER`, `SMALLINT`, `FLOAT`, `DOUBLE` `PRECISION`, `NUMERIC (p,s)`, and `DECIMAL(p,s)`.

`FLOAT` and `INTEGER` are always 32-bit, and `SMALLINT` is 16-bit. `DOUBLE` `PRECISION` is platform-dependent but generally 64-bit. Precision for `NUMERIC`/`DECIMAL` is from 1 to 15, and scale is from 1 to 15.

`DBD::InterBase` always returns all numbers as strings.

#### String data handling

InterBase supports the following string datatypes:

>     CHAR(size)      fixed length blank-padded
>     VARCHAR(size)   variable length with limit

Range of `size` is 1 to 32,767 bytes. The character set for each field may also be specified. For example:

>     CHAR(size) CHARACTER SET "ISO8859_1"
>     VARCHAR(size) CHARACTER SET "ISO8859_1"

InterBase also supports `NCHAR(size)` and `NCHAR(size)` `VARYING` as aliases for the `CHARACTER SET "ISO8859_1"` examples above.

#### Date data handling

InterBase supports one flexible date datatype: `DATE`, which includes either date, time, or date and time information. Data that will be stored as `DATE` datatype should be in format: `DD` `MON` `YYYY` `HH:MM:SS`, or `DD-MON-YYYY` `HH:MM:SS`. `DD` and `MON` parts must be supplied, other parts, if omitted, will be set to current year/time.

The `DD MON YYYY` parts can have any value from January 1, 100 AD to February 29, 32768 AD. `HH:MM:SS` ranges from 00:00:00 to 23:59:59.

The year part should be written in four digits, if it is only in two digits, then InterBase will infer the century number using a sliding window algorithm: subtracting the two-digit year number entered from the last two digits of the current year, if the absolute difference is greater than 50, then the century of the number entered is 20; otherwise, it is 19.

Fetched `DATE` values are formatted using a `strftime( )` format string. This format string can be specified as `DateFormat` attribute when invoking `prepare( )` method. If this attribute is left unspecified, then "`%c`" will be used as the format string. For example:

>     $stmt = "SELECT * FROM BDAY";
>     $opt = { 'DateFormat' => "%d %B %Y" };
>     $array_ref = $dbh->selectall_arrayref($stmt, $opt);

InterBase does not directly support SQL-92 `DATE`, `TIME`, and `TIMESTAMP` datatypes.

Date literals used by InterBase are: `NOW`, `TODAY`, `YESTERDAY`, and `TOMORROW`. For example:

>     CREATE TABLE SALES (
>         ORDER_ID INTEGER NOT NULL,
>         SHIP_DATE DATE DEFAULT "NOW" NOT NULL,
>     PRIMARY KEY(ORDER_ID));

#### LONG/BLOB data handling

InterBase supports a `BLOB` datatype. `DBD::InterBase` can handle `BLOB`s up to 4 GB, assuming you have that much memory in your system.

A BLOB column can be defined to hold either binary data or text data; if text, then a character set can also be specified. BLOB data is stored in segments, and the segment size (up to 64 KB) can also be specified for each BLOB column.

#### Other data handling issues

InterBase supports automatic conversions between datatypes wherever it is reasonable.

#### Transactions, Isolation, and Locking

InterBase supports transactions. Transaction isolation can be altered using the `SET TRANSACTION ISOLATION LEVEL x` statement. Refer to the InterBase DSQL manual for full details.

Rows returned by a `SELECT` statement can be locked to prevent them being changed by another transaction, by appending `FOR UPDATE` to the select statement. Optionally, you can specify a column list in parentheses after the `FOR UPDATE` clause.

There is no explicit `LOCK TABLE` statement.

#### SQL Dialect

#### Case sensitivity of LIKE operator

The `LIKE` operator is case-sensitive.

#### Table join syntax

Outer joins and inner joins are supported and are expressed using the ISO standard SQL syntax.

#### Table and column names

The maximum size of table and column names can't exceed 31 characters in length. Only alphanumeric characters can be used; the first character must be a letter.

InterBase converts all identifiers to uppercase.

#### Row ID

There is no "Row ID" concept.

#### Automatic key or sequence generation

A mechanism to create unique, sequential number that is automatically inserted at SQL operation such as `INSERT`, `UPDATE` is called `GENERATOR`. For example:

>     CREATE GENERATOR generator_name
>     SET GENERATOR generator_name TO integer_value

where `integer_value` is an integer value from -2**31 to 2**31 - 1. The `SET` `GENERATOR` command sets the starting value of a newly created generator, or resets the value of an existing generator.

To use the generator, InterBase's `GEN_ID` function should be invoked. For example:

>     INSERT INTO SALES (PO_NUMBER) VALUES (GEN_ID(generator_name, step))

There's no `DROP` `GENERATOR` command; here is how to delete a `GENERATOR`:

>     DELETE FROM RDB$GENERATORS WHERE RDB$GENERATOR_NAME = 'generator_name'

#### Automatic row numbering and row count limiting

Neither automatic row numbering nor row count limitations are supported.

#### Positioned updates and deletes

InterBase does not support positioned updates or deletes.

#### Parameter Binding

Parameter binding is supported directly by InterBase. `DBD::InterBase` supports the `?` placeholder style.

The TYPE attribute of the `bind_ param()` as well as `type_info()` method are not yet supported.

#### Stored Procedures

InterBase does support stored procedures, but neither `DBD::InterBase` nor `IBPerl` has support for them that yet.

#### Table Metadata

`DBD::InterBase` hasn't yet supported the `table_info()` method.

#### Driver-Specific Attributes and Methods

There are no significant `DBD::InterBase` driver-specific database handle attributes.

DBD::mysql and DBD::mSQL |

#### General Information

#### Driver version

`DBD::mysql` and `DBD::mSQL` versions 1.20xx and 1.21_xx

Version 1.20xx (even numbers) is the stable line, which is maintained for bug and portability fixes only. Version 1.21_xx (odd numbers) is used for development of the driver: all new features or interface modifications will be done in this line until it finally becomes 1.22xx.

#### Feature summary

>     Transactions                           No
>     Locking                                Yes, explicit (MySQL only)
>     Table joins                            Yes, inner and outer (inner only for mSQL)
>     LONG/LOB datatypes                     Yes, up to 4 GB
>     Statement handle attributes available  After execute(  )
>     Placeholders                           Yes, "?" (emulated)
>     Stored procedures                      No
>     Bind output values                     No
>     Table name letter case                 Depends on filesystem, stored as defined
>     Field name letter case                 Insensitive/Sensitive (MySQL/mSQL), stored
>                                            as defined
>     Quoting of otherwise invalid names     No
>     Case-insensitive "LIKE" operator       Varies, see description below
>     Server table ROW ID pseudocolumn       Yes, "_rowid" (mSQL only)
>     Positioned update/delete               No
>     Concurrent use of multiple handles     Unrestricted

#### Author and contact details

The driver author is Jochen Wiedmann. He can be contacted via the mailing list Msql-Mysql-modules@lists.mysql.com.

#### Supported database versions and options

MySQL and mSQL are freely available, small, efficient database servers. MySQL has a rich feature set while mSQL is quite minimalist.

The `DBD::mysql` driver 1.20xx supports all MySQL versions since around 3.20. The `DBD::mysql` driver 1.21_xx supports MySQL 3.22 or later.

The `DBD::mSQL` drivers 1.20xx and 1.21_xx support all mSQL versions up to and including mSQL 2.0.x.

For further information about MySQL:

> http://www.mysql.com/

For further information about mSQL:

> http://www.blnet.com/msqlpc

> http://www.hughes.com.au/

#### Differences from the DBI specification

Both `DBD::mysql` and `DBD::mSQL` do not fully parse the statement until it's executed. Thus attributes like `$sth->{NUM_OF_FIELDS}` are not available until after `$sth->execute()` has been called. This is valid behavior, but is important to note when porting applications written originally for other drivers.

Also note that many statement attributes cease to be available after fetching all the result rows or calling the `finish( )` method.

#### Connect Syntax

The `DBI->connect()` Data Source Name, or DSN, can be one of the following:

>     DBI:mysql:attrs
>     DBI:mSQL:attrs

where `attrs` is a semicolon-separated list of key=value pairs. Valid attributes include:

`database=$database`

  The database name you want to connect to.

`host=$host`

  The name of the machine running the server for the database you want to connect to, by default localhost.

`msql_configfile=$file`

  Load driver-specific settings from the given file, by default InstDir/msql.conf.

`mysql_compression=1`

  For slow connections, you may wish to compress the traffic between your client and the engine. If the MySQL engine supports it, this can be enabled by using this attribute. Default is off.

There are no driver-specific attributes applicable to the `connect()` method. The number of database and statement handles is limited by memory only. There are no restrictions on their concurrent use.

#### Datatypes

#### Numeric data handling

MySQL has five sizes of integer datatype, each of which can be signed (the default) or unsigned (by adding the word `UNSIGNED` after the type name).

Name | Bits | Signed Range | Unsigned Range
--- | --- | --- | ---
> TINYINT |
>     8
| -128..127 | 0..255
> SMALLINT |
>     16
| -32768..32767 | 0..65535
> MEDIUMINT |
>     24
| -8388608..8388607 | 0..16777215
> INTEGER |
>     32
| -2147483648..2147483647 | 0..4294967295
> BIGINT |
>     64
| -(2*63)..(2**63-1) | 0..(2**64)

The type `INT` can be used as an alias for `INTEGER`. Other aliases include `INT1=TINYINT`, `INT2=SMALLINT`, `INT3=MEDIUMINT`, `INT4=INT`, `INT8=BIGINT`, and `MIDDLEINT=MEDIUMINT`.

Note that all arithmetic is done using signed `BIGINT` or `DOUBLE` values, so you shouldn't use unsigned big integers larger than the largest signed big integer (except with bit functions). Note that `-`, `+`, and `*` will use `BIGINT` arithmetic when both arguments are `INTEGER` values. This means that if you multiply two big integers (or multiply the results from functions that return integers), you may get unexpected results if the result is bigger than 9223372036854775807.

MySQL has three main non-integer datatypes: `FLOAT`, `DOUBLE`, and `DECIMAL`. Aliases `FLOAT4` for `FLOAT` and `FLOAT8` for `DOUBLE` also work.

In what follows, the letter `M` is used for the maximum display size or PRECISION in ODBC and DBI terminology. The letter `D` is used for the number of digits that may follow the decimal point. (`SCALE` in ODBC and DBI terminology).

Maximum display size (`PRECISION`) and number of fraction digits (`SCALE`) are typically not required. For example, if you use just "`DOUBLE`," then default values will be silently inserted.

`DOUBLE(M,D)`

  A normal-size (double-precision) floating-point number. Allowable values are -1.7976931348623157e+308 to -2.2250738585072014e-308, and 2.225073858 5072014e-308 to 1.7976931348623157e+308.

  `REAL` and `DOUBLE` `PRECISION` can be used as aliases for `DOUBLE`.

`FLOAT(M,D)`

  A small (single-precision) floating-point number. Allowable values are -3.402823466e+38 to -1.175494351e-38, and -1.175494351e-38 to 3.40282346 6e+38.

`FLOAT(M)`

  A floating-point number. Precision (`M`) can be 4 or 8. `FLOAT(4)` is a single-precision number and `FLOAT(8)` is a double-precision number. These types are like the `FLOAT` and `DOUBLE` types described above. `FLOAT(4)` and `FLOAT(8)` have the same ranges as the corresponding `FLOAT` and `DOUBLE` types, but their display size and number of decimals is undefined.

`DECIMAL(M,D)`

  The `DECIMAL` type is an unpacked floating-point number type. `NUMERIC` is an alias for `DECIMAL`. It behaves like a `CHAR` column; "unpacked" means the number is stored as a string, using one character for each digit of the value, and the decimal point. If `D` is 0, values will have no decimal point or fractional part. The maximum range of `DECIMAL` values is the same as for `DOUBLE`, but the actual range for a given `DECIMAL` column may be constrained by the choice of `M` and `D`.

`NUMERIC` can be used as an alias for `DECIMAL`.

The numeric datatypes supported by mSQL are much more restricted:

> `INTEGER` corresponds to MySQL's `INTEGER` type.

> `UINT` corresponds to MySQL's `INTEGER` `UNSIGNED` type.

> `REAL` corresponds to MySQL's `REAL` type.

The driver returns all datatypes, including numbers, as strings. It thus puts no restriction on size of `PRECISION` or `SCALE`.

#### String data handling

The following string types are supported by MySQL, quoted from mysql.info where M denotes the maximum display size or `PRECISION`:

`CHAR(M)`

  A fixed-length string that is always right-padded with spaces to the specified length. The range of `M` is 1 to 255 characters.

`VARCHAR(M)`

  A variable-length string. Note that trailing spaces are removed by the database when the value is stored (this differs from the ANSI SQL specification). The range of `M` is 1 to 255 characters.

`ENUM('value1','value2',...)`

  An enumeration. A string object that can have only one value, chosen from the specified list of values (or NULL). An ENUM can have a maxiumum of 65535 distinct values.

`SET('value1','value2',...)`

  A set. A string object that can have zero or more values, each of which must be chosen from the specified list of values. A SET can have a maximum of 64 members.

`CHAR` and `VARCHAR` types have a limit of 255 bytes. Binary characters, including the NUL byte, are supported by all string types. (Use the `$dbh->quote( )` method for literal strings).

These aliases are also supported:

>     BINARY(num)      CHAR(num) BINARY
>     CHAR VARYING     VARCHAR
>     LONG VARBINARY   BLOB
>     LONG VARCHAR     TEXT
>     VARBINARY(num)   VARCHAR(num) BINARY

With `DBD::mysql`, the `ChopBlanks` attribute is always on. The MySQL engine itself removes spaces from the string's right end. Another "feature" is that `CHAR` and `VARCHAR` columns are always case-insensitive in comparisons and sort operations, unless you use the `BINARY` attribute, as in:

>     CREATE TABLE foo (A VARCHAR(10) BINARY)

With versions of MySQL after 3.23, you can perform a case-insensitve comparison of strings with the `BINARY` operator modifier:

>     SELECT * FROM table WHERE BINARY column = "A"

National language characters are handled in comparisons following the coding system that was specified at compile-time, by default ISO-8859-1. Non-ISO coding systems, and in particular UTF-16, are not supported.

Strings can be concatenated using the `CONCAT(s1, s2, ...)` SQL function.

The mSQL engine (and hence the `DBD::mSQL` driver) supports only the `CHAR(M)` string type, which corresponds to the MySQL's `VARCHAR(M)` type, and a `TEXT(M)` type, which is a cross between a `CHAR` and a `BLOB`. All string types have trailing spaces removed by mSQL. Also, mSQL has no way to concatenate strings.

#### Date data handling

The following date and time types are supported by MySQL, and quoted from mysql.info:

`DATE`

  A date. The supported range is `0000-01-01` to `9999-12-31`. MySQL displays `DATE` values in `YYYY-MM-DD` format, but allows you to assign values to `DATE` columns using these formats:

  >     YYMMDD
  >     YYYYMMDD
  >     YY.MM.DD
  >     YYYY.MM.DD

  Where `.` may be any non-numerical separator, and a two-digit year is assumed to be 20`YY` if `YY` is less than 70.

`DATETIME`

  A date and time combination. The supported range is `0000-01-01` `00:00:00` to `9999-12-31` `23:59:59`. MySQL displays `DATETIME` values in `YYYY-MM-DD` `HH:MM:SS` format, but allows you to assign values to `DATETIME` columns using the formats shown for `DATE` above but with `HH:MM:SS` appended.

`TIMESTAMP(M)`

  A timestamp. The range is `1970-01-01` `00:00:00` to sometime in the year 2032 (or 2106, depending on the OS specific type `time_t`). MySQL displays `TIMESTAMP` values in `YYYYMMDDHHMMSS`, `YYMMDDHHMMSS`, `YYYYMMDD`, or `YYMMDD` format, depending on whether `M` is 14 (or missing), 12, 8, or 6, but allows you to assign values to `TIMESTAMP` columns using either strings or numbers. This output format behavior disagrees with the manual, so check your version because the behavior may change.

  A `TIMESTAMP` column is useful for recording the time of an `INSERT` or `UPDATE` operation because it is automatically set to the time of the last operation if you don't give it a value yourself. You can also set it to the current time by giving it a `NULL` value.

`TIME`

  A time. The range is `-838:59:59` to `838:59:59`. MySQL displays `TIME` values in `HH:MM:SS` format. You can assign values to `TIME` columns using these formats: `[[[DAYS] [H]H:]MM:]SS[.fraction]` or `[[[[[H]H]H]H]MM]SS [.fraction]`.

`YEAR`

  A year. The allowable values are 1901-2155, and 0000 in the four-digit year format, and 1970-2069 if you use the two-digit year format (70-69). On input, two-digit years in the range 00-69 are assumed to be 2000-2069. (`YEAR` is a new type for MySQL 3.22.)

If you are using two-digit years as in `YY-MM-DD` (dates) or `YY` (years), then they are converted into 2000-2069 and 1970-1999, repectively. Thus, MySQL has no Y2K problem, but a Y2070 problem!

In MySQL 3.23, this feature will be changed to 2000-2068 and 1969-1999, following the X/Open Unix standard.[73]

> [73]See http://www.unix-systems.org/version2/whatsnew/year2000.html.

The `NOW()` function, and its alias `SYSDATE`, allow you to refer to the current date and time in SQL.

The `DATE_FORMAT(date,` `format)` function can be used to format date and time values using `printf`-like format strings.

MySQL has a rich set of functions operating on dates and times, including `DAYOFWEEK(date)` (1 = Sunday, ..., 7 = Saturday), `WEEKDAY(date)` (0 = Monday, ..., 6 = Sunday), `DAYOFMONTH(date)`, `DAYOFYEAR(date)`, `MONTH(date)`, `DAYNAME (date)`, `MONTHNAME(date)`, `WEEK(date)`, `YEAR(date)`, `HOUR(time)`, `MINUTE (time)`, `SECOND(time)`, `DATE_ADD(date, interval)` (`interval` being something like "`2 HOURS`"), and `DATE_SUB(date, interval)`.

The following SQL expression can be used to convert an integer "seconds since 1-jan-1970 GMT" value to the corresponding database date/time:

>     FROM_UNIXTIME(seconds_since_epoch)

and the reverse:

>     UNIX_TIMESTAMP(timestamp)

MySQL does no automatic time zone adjustments.

The mSQL database supports these date/time types:

>     DATE  - corresponds to MySQL's DATE type
>     TIME  - corresponds to MySQL's TIME type

The only date format supported by mSQL is `DD-MMM-YYYY`, where `MMM` is the three-character English abbreviation for the month name. The only time format supported by mSQL is `HH:MM:SS`.

#### LONG/BLOB data handling

These are MySQL's BLOB types, quoted from mysql.info :

>     TINYBLOB   / TINYTEXT    maximum length of 255 (2**8 - 1)
>     BLOB       / TEXT        maximum length of 65535 (2**16 - 1)
>     MEDIUMBLOB / MEDIUMTEXT  maximum length of 16777215 (2**24 - 1)
>     LONGBLOB   / LONGTEXT    maximum length of 4294967295 (2**32 - 1)

Binary characters in all BLOB types are allowed. The `LongReadLen` and `LongTruncOk` types are not supported.

The maximum length of `bind_ param( )` parameter values is only limited by the maximum length of an SQL statement. By default that's 1MB but can be extended to just under 24 MB by changing the mysqld variable `max_allowed_packet`.

No `TYPE` or other attributes need to be given to `bind_ param( )` when binding these types.

The only `BLOB` type supported by mSQL is `TEXT`. This is a cross between a traditional `VARCHAR` type and a BLOB. An average width is specified, and data longer than average is automatically stored in an overflow area in the table.

#### Other data handling issues

The driver versions 1.21_xx and above do support the `type_info( )` method.

MySQL supports automatic conversions between datatypes wherever it's reasonable. mSQL, on the other hand, supports none.

#### Transactions, Isolation, and Locking

Both mSQL and MySQL do not support transactions.

Since both mSQL and MySQL currently execute statements from multiple clients one at a time (atomic), and don't support transactions, there's no need for a default locking behavior to protect transaction isolation.

With MySQL, locks can be explicitly obtained on tables. For example:

>     LOCK TABLES table1 READ, table2 WRITE

Locks are released with any subsequent `LOCK TABLES` statement, by dropping a connection or with an explicit command:

>     UNLOCK TABLES

There are also user-defined locks that can be manipulated with the `GET_LOCK()` and `RELEASE_LOCK()` SQL functions. You can't automatically lock rows or tables during `SELECT` statements; you have to do it explicitly.

And, as you might guess, mSQL doesn't support any kind of locking at the moment.

#### SQL Dialect

#### Case sensitivity of LIKE operator

With MySQL, case-sensitivity of all character comparison operators, including `LIKE`, requires on the presence of the `BINARY` attribute on at least one of the fields -- either on the field type in the `CREATE` `TABLE` statement or on the field name in the comparison operator. However, you can always force case-insensitivity using the `TOLOWER` function.

mSQL has three `LIKE` operators: `LIKE` is case-sensitive, `CLIKE` is case-insensitive, and `RLIKE` uses Unix style regular expressions.

#### Table join syntax

Joins are supported with the usual syntax:

>     SELECT * FROM a,b WHERE a.field = b.field

or, alternatively:

>     SELECT * FROM a JOIN b USING field

Outer joins are supported by MySQL, not mSQL, with:

>     SELECT * FROM a LEFT OUTER JOIN b ON condition

Outer joins in MySQL are always left outer joins.

#### Table and column names

MySQL table and column names may have at most 64 characters. mSQL table and column names are limited to 35 characters.

With MySQL, you can put single quotes around table or column names (you can use the standard double quotes if the database is started with the `--ansi-mode` option). You need to do that if the name contains special characters or matches a reserved word. Quoting identifiers isn't supported by mSQL.

Table names are limited by the fact that tables are stored in files and the table names are really file names. In particular, the case-sensitivity of table names depends on the underlying file system and some characters like `.` and `/` are not allowed.

Column names are case-insensitive with MySQL and case-sensitive with mSQL, but both engines store them without case conversions.

Names can include national character set characters (with the eighth bit set) in MySQL but not mSQL.

#### Row ID

MySQL doesn't have row IDs. mSQL has a pseudocolumn `_rowid`.

The mSQL `_rowid` column value is numeric and, since mSQL doesn't automatically convert strings to numbers, you must take care not to quote the value when using it in later `SELECT` statements.

Note that because transactions and locking aren't supported, there's a greater risk that the row identified by a `_rowid` value you just fetched may have been deleted and possibly replaced by a different row by the time you use the row ID value moments later.

#### Automatic key or sequence generation

All MySQL integer table fields can have an `AUTO_INCREMENT` attribute. That is, given a table:

>     CREATE TABLE a (
>       id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
>       ...)

and a statement:

>     INSERT INTO a (id, ...) VALUES (NULL, ...)

a unique ID will be generated automatically (similarly, if the ID field had not been mentioned in the insert statement at all). The generated ID can later be retrieved with:

>     $sth->{mysql_insertid}                (1.21_xx)
>     $sth->{insertid}                      (1.20_xx)

or, if you've used `$dbh->do` and not `prepare`/`execute`, then use:

>     $dbh->{mysql_insertid}                (1.21_xx)
>     $dbh->do("SELECT LAST_INSERT_ID(  )");  (1.20_xx)

MySQL does not support sequence generators directly, but they can be emulated with a little care (refer to the MySQL manual for details). For example:

>     UPDATE seq SET id=last_insert_id(id+1)

The mSQL database supports sequence generators, but just one per table. After executing:

>     CREATE SEQUENCE on A

you can later do:

>     SELECT _seq FROM A

to fetch the value. You can't refer directly to the sequence from an insert statement; instead, you have to fetch the sequence value and then execute an insert with that value.

#### Automatic row numbering and row count limiting

Neither engine supports automatic row numbering of `SELECT` statement results.

Both mSQL and MySQL support row count limiting with:

>     SELECT * FROM A LIMIT 10

to retrieve the first 10 rows only, but only MySQL supports:

>     SELECT * FROM A LIMIT 20, 10

to retrieve rows 20-29, with the count starting at 0.

#### Positioned updates and deletes

Neither positioned updates nor deletes are supported by MySQL or mSQL.

#### Parameter Binding

Neither engine supports placeholders, but the `DBD::mysql` and `DBD::mSQL` drivers provide full emulation. Question marks are used as placeholders, as in:

>     $dbh->do("INSERT INTO table VALUES (?, ?)", undef, $id, $name);

The `:1` placeholder style is not supported.

In the above example, the driver attempts to guess the datatype of the inserted values by looking at Perl's own internal string versus number datatype hints. This is fine with MySQL, because MySQL can deal with expressions like:

>     INSERT INTO table (id_number) VALUES ('2')

where `id_number` is a numeric column. But this doesn't apply to mSQL, which would treat that as an error. So you sometimes need to force a datatype, either by using:

>     $dbh->do("INSERT INTO table VALUES (?, ?)", undef, int($id), "$name");

or by using the `TYPE` attribute of the `bind_ param()` method:

>     use DBI qw(:sql_types);
>     $sth = $dbh->prepare("INSERT INTO table VALUES (?, ?)");
>     $sth->bind_param(1, $id,   SQL_INTEGER);
>     $sth->bind_param(2, $name, SQL_VARCHAR);
>     $sth->execute();

Unsupported values of the `TYPE` attribute do not currently generate a warning.

#### Stored Procedures

Neither mSQL nor MySQL have a concept of stored procedures, although there are plans to add some stored procedure features to MySQL.

#### Table Metadata

The 1.21_xx version of the drivers was the first to support the `table_info()` method.

To obtain information on a generic table, you can use the query:

>     LISTFIELDS $table

This will return a statement handle without result rows. The `TYPE`, `NAME`, ... attributes are describing the table.

With MySQL you can use:

>     SHOW INDEX FROM $table

to retrieve information on a table's indexes, in particular a primary key. The information will be returned in rows. The `DBD::mSQL` driver does support a similar thing using:

>     LISTINDEX $table $index

with `$index` being the name of a given index.

#### Driver-Specific Attributes and Methods

The following driver-specific database handle attributes are supported:

`mysql_info`
`mysql_thread_id`
`mysql_insertid`

  These attributes correspond to the C calls `mysql_info()`, `mysql_thread_id()`, and `mysql_insertid()`, respectively.

The following driver-specific statement handle attributes are supported:

`mysql_use_result`
`mysql_store_result`

  With `DBD::mysql`, there are two different ways the driver fetches results from the server. With `mysql_store_result` enabled, it fetches all rows at once, creating a result table in memory and returns it to the caller (a 100% row cache).

  With `mysql_use_result`, it returns rows to the application as they are fetched. This is less memory-consuming on the client side, but should not be used in situations where multiple people can query the database, because it can block other applications. (Don't confuse that with locking!)

`mysql_insertid`

  A previously generated `auto_increment` column value, if any.

`mysql_is_blob`
`mysql_is_key`
`msql_is_num`
`mysql_is_num`
`msql_is_ pri_key`
`mysql_is_ pri_key`

  These attributes return an array ref with the given flags set for any column of the result set. Note you may use these with the `LISTFIELDS` query to obtain information about the columns of a table.

`mysql_max_length`

  Unlike the `PRECISION` attribute, this returns the true actual maximum length of the particular data in the current result set. This can be helpful, for example, when displaying ASCII tables.

  This attribute doesn't work with `mysql_use_result` enabled, since it needs to look at all the data.

`msql_table`
`mysql_table`

  Similar to `NAME`, but the table names and not the column names are returned.

`msql_type`
`mysql_type`

  Similar to `TYPE`, but they return the respective engine's native type.

`msql_type_name`
`mysql_type_name`

  Similar to `msql_type` and `mysql_type`, but column names are returned, that you can use in a `CREATE TABLE` statement.

A single private method called `admin()` is supported. It provides a range of administration functions:

>     $rc = $drh->func('createdb', $db, $host, $user, $password, 'admin');
>     $rc = $drh->func('dropdb',   $db, $host, $user, $password, 'admin');
>     $rc = $drh->func('shutdown',      $host, $user, $password, 'admin');
>     $rc = $drh->func('reload',        $host, $user, $password, 'admin');

>     $rc = $dbh->func('createdb', $database, 'admin');
>     $rc = $dbh->func('dropdb',   $database, 'admin');
>     $rc = $dbh->func('shutdown',            'admin');
>     $rc = $dbh->func('reload',              'admin');

These correspond to the respective commands of `mysqladmin` and `msqladmin`.

DBD::ODBC |

#### General Information

#### Driver version

`DBD::ODBC` version 0.20

#### Feature summary

Because `DBD::ODBC` acts as an interface to other database drivers, much of its behavior is governed by those drivers.

>     Transactions                           Dependent on connected data source
>     Locking                                Dependent on connected data source
>     Table joins                            Dependent on connected data source
>     LONG/LOB datatypes                     Dependent on connected data source
>     Statement handle attributes available  After prepare(  )
>     Placeholders                           Yes
>     Stored procedures                      Yes
>     Bind output values                     No
>     Table name letter case                 Dependent on connected data source
>     Field name letter case                 Dependent on connected data source
>     Quoting of otherwise invalid names     Dependent on connected data source
>     Case-insensitive "LIKE" operator       Dependent on connected data source
>     Server table ROW ID pseudocolumn       Dependent on connected data source
>     Positioned update/delete               Yes
>     Concurrent use of multiple handles     Dependent on connected data source

#### Author and contact details

The driver authors are Jeff Urlwin and Tim Bunce. The original work was based upon an early version of Thomas Wenrich's `DBD::Solid`. The authors can be contacted via the dbi-users mailing list.

#### Supported database versions and options

The `DBD::ODBC` module supports ODBC Version 2.x and 3.x on Unix and Win32. For all platforms, both an ODBC driver manager and an ODBC driver are required in addition to the `DBD::ODBC` module.

For Win32, the driver manager is included with the operating system. For Unix and variants, the iODBC driver manager source is included in the `iodbcsrc` directory. While iODBC acts as the driver manager, you still have to find an actual driver for your platform and database.

Driver providers include:

> Intersolv: http://www.intersolv.com

> OpenLink: http://www.openlinksw.com

There are other vendors; this is not an exhaustive list. Other related ODBC links include:

> http://www.genix.net/unixODBC

> http://www.openlinksw.com/iodbc

> http://www.microsoft.com/data/odbc

To subscribe to the freeodbc development mailing list, send a message to freeodbc-request@as220.org with just the word `subscribe` in the body of the message.

#### Differences from the DBI specification

`DBD::ODBC` does not currently support "out" parameter binding. That should be fixed in a later release.

#### Connect Syntax

The `DBI->connect()` Data Source Name, or DSN, has the following forms:

>     dbi:ODBC:odbc_dsn
>     dbi:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=\\server\share\access.mdb

In the first example above, `odbc_dsn` is an ODBC Data Source Name (DSN). An ODBC DSN is simply a name you use to refer to a set of driver-specific connection parameters defined elsewhere. Connection parameters typically include the name of the ODBC driver to use, the database name, and any required connection details.

Under Win32, the best method of creating an ODBC DSN is by using the ODBC32 applet on the Windows Control Panel. Under Unix variants, you typically need to edit a text file called .odbc.ini in your home directory. Refer to your driver manager documentation for more details.

The second connection example above uses the driver-specific connection string. By specifying all the required information, you can bypass the need to use a previously defined DSN. In our example we're using the "`Microsoft Access Driver (*.mdb)`" driver to reach the data in the \\server\share\access.mdb Access database file.

There are currently no driver-specific attributes for the `DBI->connect()` method.

Most ODBC drivers and databases let you make multiple concurrent database connections to the same database. A few do not.

Some ODBC drivers and databases, most notably Sybase and SQL Server, do not let you prepare and execute a new statement handle while still fetching data from another statement handle associated with the same database handle.

#### Datatypes

#### Numeric data handling

The numeric data handling for ODBC is dependent upon a variety of factors. One of those critical factors is the end database. For example, Oracle supports different numeric types than Sybase which, in turn, supports different numeric types than a CSV file. You will need to read your database documentation for more information.

Unfortunately, the second critical set of factors are the ODBC driver manufacturer and version of the driver. For example, I have seen a great variety in the handling of numeric values between versions of Oracle's ODBC drivers. What works with one version, sadly, may not work with even a later version of Oracle's drivers. You will need to read your ODBC driver documentation for more information.

The DBI `type_info( )` and `type_info_all( )` methods provide information about the datatypes supported by the database and driver being used.

#### String data handling

As with numeric handling, string data handling is dependent upon the database and driver. Please see "Numeric Data Handling" above for more information.

Strings can be concatenated using the `CONCAT(s1,s2)` SQL function.

#### Date data handling

As with numeric handling, date data handling is dependent upon the database and driver. Please see "Numeric Data Handling" above for more information.

You can use ODBC escape sequences to define a date in a database-independent way. For example, to insert a date of Jan 21, 1998 into a table, you could use:

>     INSERT INTO table_name (date_field) VALUES ({d '1998-01-21'});

You can use placeholders within escape sequences instead of literal values. For example:

>     INSERT INTO table_name (date_field) VALUES ({d ?});

Similar escape sequences are defined for other date/time types. Here's the full set:

>     {d 'YYYY-MM-DD'}                    -- date
>     {t 'HH:MM:SS'}                      -- time
>     {ts 'YYYY-MM-DD HH:MM:SS'}          -- timestamp
>     {ts 'YYYY-MM-DD HH:MM:SS.FFFFFFF'}  -- timestamp

If you specify a DATE value without a time component, the default time is 00:00:00 (midnight). There is also an interval escape clause which is constructed like this:

>     {interval [+|-] 'value' [interval_qualifier]}

For example:

>     {interval '200-11' YEAR(3) TO MONTH}

Please see an ODBC reference guide for more information.

The current date and time on the server can be found by using an ODBC scalar function escape sequence to call the appropriate function. For example:

>     INSERT INTO table_name (date_field) VALUES ({fn CURDATE});

The `{fn ...}` escape sequence isn't required if the entire SQL statement conforms to the level of SQL-92 grammar supported by your ODBC driver.

Other related functions include `CURTIME( )`, `NOW( )`, `CURRENT_DATE( )`, `CURRENT_TIME( )`, and `CURRENT_TIMESTAMP( )`. The last three require an ODBC v3 driver.

Other date/time related functions include: `DAYNAME( )`, `DAYOFMONTH( )`, `DAYOFWEEK( )`, `DAYOFYEAR( )`, `EXTRACT( )`, `HOUR( )`, `MINUTE( )`, `MONTH( )`, `MONTHNAME( )`, `SECOND( )`, `WEEK( )`, and `YEAR( )`.

Basic date/time arithmetic can be performed using the `TIMESTAMPADD( )` and `TIMESTAMPDIFF( )` functions.

The following SQL expression can be used to convert an integer "seconds since 1-jan-1970" value to the corresponding database date/time:

>     TIMESTAMPADD(SQL_TSI_SECOND, seconds_since_epoch, {d '1970-01-01'})

to do the reverse you can use:

>     TIMESTAMPDIFF(SQL_TSI_SECOND, {d '1970-01-01'}, date_field)

ODBC itself does not have any support for time zones, though the database to which you are connected may.

#### LONG/BLOB data handling

Support for `LONG`/`BLOB` datatypes and their maximum lengths are very dependent on the database to which you are connected.

The `LongReadLen` and `LongTruncOk` attributes work as defined. However, the driver implementations do affect this. Some drivers do not properly indicate that they have truncated the data, or they have more data available than was actually returned. The `DBD::ODBC` tests attempt to determine correct support for this.

No special handling is required for `LONG`/`BLOB` datatypes. They can be treated just like any other field when fetching or inserting, etc.

#### Other data handling issues

The `DBD::ODBC` driver supports the `type_info( )` method.

#### Transactions, Isolation, and Locking

`DBD::ODBC` supports transactions if the databases you are connected to supports them.

Supported isolation levels, the default isolation level, and locking behavior are all dependent on the database to which you are connected.

#### SQL Dialect

Because `DBD::ODBC` acts as an interface to other database drivers, the following issues are governed by those drivers and the databases they connect to:

- Case-sensitivity of `LIKE` operator

- Table and column names

- Row ID

- Automatic key or sequence generation

- Automatic row numbering and row count limiting

For more information, refer to the documentation for the drivers and the database being used.

#### Table join syntax

Table join syntax is partly dependent on the database to which you are connected and the ODBC driver you are using. The ODBC standard SQL defines the standard syntax for inner joins and an escape sequence to use for outer joins:

>     {oj outer_join}

where `outer_ join` is defined as:

>     table_name [LEFT | RIGHT | FULL]
>       OUTER JOIN [ table_name | outer_join] ON condition

An outer join request must appear after the `FROM` clause of a `SELECT` but before a `WHERE` clause, if one exists.

#### Positioned updates and deletes

This is dependent on the database to which you are connected. Positioned updates and deletes are supported in ODBC SQL using the `WHERE` `CURRENT` `OF` syntax.

For example:

>     $dbh->do("UPDATE ... WHERE CURRENT OF $sth->{CursorName}");

#### Parameter Binding

Parameter binding is supported by `DBD::ODBC` if the underlying ODBC driver driver supports it. Only the standard `?` style of placeholders is supported.

The `TYPE` attribute to the `bind_ param()` method is supported.

#### Stored Procedures

Stored procedures can be called using the following ODBC escape sequence:

>     {call procedure1_name}
>     {call procedure2_name(?, ?)}
>     {?= call procedure3_name(?, ?)}

The last form would be used to return values from the procedure, but `DBD::ODBC` currently does not support output parameters.

#### Table Metadata

`DBD::ODBC` supports the `table_info()` method.

`DBD::ODBC` also supports many of the ODBC metadata functions that can be used to discover information about the tables within a database. These can be accessed as driver-specific private methods:

>     SQLGetTypeInfo   --  $dbh->func(xxx,        'GetTypeInfo')
>     SQLDescribeCol   --  $sth->func(colno,      'DescribeCol')
>     SQLColAttributes --  $sth->func(xxx, colno, 'ColAttributes')
>     SQLGetFunctions  --  $dbh->func(xxx,        'GetFunctions')
>     SQLColumns       --  $dbh->func(catalog, schema, table, column, 'columns')
>     SQLStatistics    --  $dbh->func(catalog, schema, table, unique, 'Statistics')
>     SQLPrimaryKeys   --  $dbh->func(catalog, schema, table, 'PrimaryKeys')
>     SQLForeignKeys   --  $dbh->func(pkc, pks, pkt, fkc, fks, fkt, 'ForeignKeys')

The DBI will provide standard methods for these soon, possibly by the time you read this book.

#### Driver-Specific Attributes and Methods

`DBD::ODBC` has no driver-specific handle attributes.

In addition to the private methods described in "Table Metadata" above, the `GetInfo()` private method can be used to discover many details about the driver and database you are using.

DBD::Oracle |

#### General Information

#### Driver version

`DBD::Oracle` version 1.03

#### Feature summary

>     Transactions                           Yes
>     Locking                                Yes, implicit and explicit
>     Table joins                            Yes, inner and outer
>     LONG/LOB datatypes                     Yes, up to 4 GB
>     Statement handle attributes available  After prepare(  )
>     Placeholders                           Yes, "?" and ":1" styles (native)
>     Stored procedures                      Yes
>     Bind output values                     Yes
>     Table name letter case                 Insensitive, stored as uppercase
>     Field name letter case                 Insensitive, stored as uppercase
>     Quoting of otherwise invalid names     Yes, via double quotes
>     Case-insensitive "LIKE" operator       No
>     Server table ROW ID pseudocolumn       Yes, "ROWID"
>     Positioned update/delete               No
>     Concurrent use of multiple handles     Unrestricted

#### Author and contact details

The driver author is Tim Bunce. He can be contacted via the dbi-users mailing list.

#### Supported database versions and options

The `DBD::Oracle` module supports both Oracle 7 and Oracle 8.

Building for Oracle 8 defaults to use the new Oracle 8 OCI interface, which enables use of some Oracle 8 features including LOBs and "`INSERT` `...` `RETURNING` `...`".

An emulation module for the old Perl4 oraperl software is supplied with `DBD::Oracle`, making it very easy to upgrade oraperl scripts to Perl5.

For further information about Oracle, refer to:

> http://www.oracle.com

> http://technet.oracle.com

#### Differences from the DBI specification

`DBD::Oracle` has no known significant differences in behavior from the current DBI specification.

#### Connect Syntax

The `DBI->connect()` Data Source Name, or DSN, can be one of the following:

>     dbi:Oracle:tnsname
>     dbi:Oracle:sidname
>     dbi:Oracle:host=hostname;sid=sid

Some other less common formats also work if supported by the Oracle client version being used.

There are no significant driver-specific attributes for the `DBI->connect()` method.

`DBD::Oracle` supports an unlimited number of concurrent database connections to one or more databases.

#### Datatypes

#### Numeric data handling

Oracle has only one flexible underlying numeric type, NUMBER. But Oracle does support several ANSI standard and IBM datatype names as aliases, including:

>     INTEGER      = NUMBER(38)
>     INT          = NUMBER(38)
>     SMALLINT     = NUMBER(38)
>     DECIMAL(p,s) = NUMBER(p,s)
>     NUMERIC(p,s) = NUMBER(p,s)
>     FLOAT        = NUMBER
>     FLOAT(b)     = NUMBER(p)   where b is the binary precision, 1 to 126
>     REAL         = NUMBER(18)

The NUMBER datatype stores positive and negative fixed and floating-point numbers with magnitudes between 1.0 × 10-130 and 9.9...9 × 10125 (38 nines followed by 88 zeroes), with 38 digits of precision.

You can specify a fixed-point number using the following form: `NUMBER(p,s)` where `s` is the scale, or the number of digits to the right of the decimal point. The scale can range from -84 to 127.

You can specify an integer using `NUMBER(p)`. This is a fixed-point number with precision `p` and scale 0. This is equivalent to `NUMBER(p,0)`.

You can specify a floating-point number using `NUMBER`. This is a floating-point number with decimal precision 38. A scale value is not applicable for floating-point numbers.

`DBD::Oracle` always returns all numbers as strings. Thus the driver puts no restriction on size of `PRECISION` or `SCALE`.

#### String data handling

Oracle supports the following string datatypes:

>     VARCHAR2(size)
>     NVARCHAR2(size)
>     CHAR
>     CHAR(size)
>     NCHAR
>     NCHAR(size)
>     RAW(size)

The `RAW` type is presented as hexadecimal characters. The contents are treated as non-character binary data and thus are never "translated" by character set conversions or gateway interfaces.

`CHAR` types and the `RAW` type have a limit of 2000 bytes. For `VARCHAR` types the limit is 2000 bytes in Oracle 7 and 4000 in Oracle 8.

The `NVARCHAR2` and `NCHAR` variants hold string values of a defined national character set (Oracle 8 only). For those types the maximum number of characters stored may be lower when using multibyte character sets.

The `CHAR` and `NCHAR` types are fixed length and blank padded.

Oracle automatically converts character data between the character set of the database defined when the database was created and the character set of the client, defined by the `NLS_LANG` parameter for the `CHAR` and `VARCHAR2` types or the `NLS_NCHAR` parameter for the `NCHAR` and `NVARCHAR2` types.

`CONVERT(string,` `dest_char_set,` `source_char_set)` can be used to convert strings between character sets. Oracle 8 supports 180 storage character sets. UTF-8 is supported. See the "National Language Support" section of the Oracle Reference manual for more details on character set issues.

Strings can be concatenated using either the `CONCAT(s1,s2,...)` SQL function or the `||` operator.

#### Date data handling

Oracle supports one flexible date/time datatype: `DATE`. A `DATE` can have any value from January 1, 4712 BC to December 31, 4712 AD with a one second resolution.

Oracle supports a very wide range of date formats and can use one of several calendars (Arabic Hijrah, English Hijrah, Gregorian, Japanese Imperial, Persian, ROC Official (Republic of China), and Thai Buddha). We'll only consider the Gregorian calendar here.

The default output format for the `DATE` type is defined by the `NLS_DATE_FORMAT` configuration parameter, but it's typically `DD-MON-YY`, e.g., 20-FEB-99 in most western installations. The default input format for the `DATE` type is the same as the output format. Only that one format is recognized.

If you specify a `DATE` value without a time component, the default time is 00:00:00 (midnight). If you specify a `DATE` value without a date, the default date is the first day of the current month. If a date format that has a two-digit year, such as the `YY` in `DD-MON-YY` (a common default) then the date returned is always in the current century. The `RR` format can be used instead to provide a fifty-year pivot.

The default date format is specified either explicitly with the initialization parameter `NLS_DATE_FORMAT` or implicitly with the initialization parameter `NLS_TERRITORY`. For information on these parameters, see Oracle8 Reference.

You can change the default date format for your session with the `ALTER` `SESSION` command. For example:

>     ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY'

The `TO_DATE( )` function can be used to parse a character string containing a date in a known format. For example:

>     UPDATE table SET date_field = TO_DATE('1999-02-21', 'YYYY-MM-DD')

The `TO_CHAR( )` function can be used to format a date. For example:

>     SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL

The current date/time is returned by the `SYSDATE( )` function.

You can add numbers to `DATE` values. The number is interpreted as numbers of days; for example, `SYSDATE` `+` `1` is this time tomorrow, and `SYSDATE` `-` `(3/1440)` is three minutes ago. You can subtract two dates to find the difference, in days, between them.

Oracle provides a wide range of date functions including `ROUND( )`, `TRUNC( )`, `NEXT_DAY( )`, `ADD_MONTHS( )`, `LAST_DAY( )` (of the month), and `MONTHS_BETWEEN( )`.

The following SQL expression can be used to convert an integer "seconds since 1-jan-1970" value to the corresponding database date/time:

>     to_date(trunc(:unixtime/86400, 0) + 2440588, 'J') -- date part
>     +(mod(:unixtime,86400)/86400)                     -- time part

To do the reverse you can use:

>     (date_time_field - TO_DATE('01-01-1970','DD-MM-YYYY')) * 86400

Oracle does no automatic time zone adjustments. However it does provide a `NEW_TIME( )` function that calculates time zone adjustments for a range of time zones. `NEW_TIME(d,` `z1,` `z2)` returns the date and time in time zone `z2` when the date and time in time zone `z1` are represented by `d`.

#### LONG/BLOB data handling

Oracle supports these `LONG`/`BLOB` datatypes:

>     LONG      - Character data of variable length
>     LONG RAW  - Raw binary data of variable length
>     CLOB      - A large object containing single-byte characters
>     NCLOB     - A large object containing national character set data
>     BLOB      - Binary large object
>     BFILE     - Locator for external large binary file

The `LONG` types can hold up to 2 GB. The other types (`LOB` and `FILE`) can hold up to 4 GB. The `LOB` and `FILE` types are only available when using Oracle 8 OCI.

The `LONG` `RAW` and `RAW` types are passed to and from the database as strings consisting of pairs of hex digits.

The `LongReadLen` and `LongTruncOk` attributes work as defined. However, the LongReadLen attribute seems to be limited to 65535 bytes on most platforms when using Oracle 7. Building `DBD::Oracle` with Oracle 8 OCI raises that limit to 4 GB.

The maximum length of `bind_ param( )` parameter value that can be used to insert LONG data seems to be limited to 65535 bytes on most platforms when using Oracle 7. Building `DBD::Oracle` with Oracle 8 OCI raises that limit to 4 GB.

The `TYPE` attribute value `SQL_LONGVARCHAR` indicates an Oracle `LONG` type. The value `SQL_LONGVARBINARY` indicates an Oracle `LONG` `RAW` type. These values are not always required but their use is strongly recommended.

No other special handling is required for `LONG`/`BLOB` datatypes. They can be treated just like any other field when fetching or inserting, etc.

#### Other data handling issues

The `DBD::Oracle` driver supports the `type_info( )` method.

Oracle supports automatic conversions between datatypes wherever it's reasonable.

#### Transactions, Isolation, and Locking

`DBD::Oracle` supports transactions. The default transaction isolation level is READ COMMITED.

Oracle supports READ COMMITED and SERIALIZABLE isolation levels. The level may be changed once per-transaction by executing a `SET` `TRANSACTION ISOLATION` `LEVEL` `x` statement (where `x` is the name of the isolation level required).

Oracle also supports transaction-level read consistency. This can be enabled by issuing a `SET` `TRANSACTION` statement with the READ ONLY option.

In Oracle, the default behavior is that a lock never prevents other users from querying the table. A query never places a lock on a table. Readers never block writers, and writers never block readers.

Rows returned by a `SELECT` statement can be locked to prevent them from being changed by another transaction by appending `FOR UPDATE` to the `SELECT` statement. Optionally, you can specify a column list in parentheses after the `FOR UPDATE` clause.

The `LOCK TABLE table_name IN lock_mode` statement can be used to apply an explicit lock on an entire table. A range of row and table locks are supported.

#### SQL Dialect

#### Case sensitivity of LIKE operator

The `LIKE` operator is case-sensitive.

#### Table join syntax

Oracle supports inner joins with the usual syntax:

>     SELECT * FROM a, b WHERE a.field = b.field

To write a query that performs an outer join of tables A and B and returns all rows from A, the Oracle outer join operator (`+` ) must be applied to all column names of B that appear in the join condition. For example:

>     SELECT customer_name, order_date
>     FROM customers, orders
>     WHERE customers.cust_id = orders.cust_id (+);

For all rows in the customer's table that have no matching rows in the orders table, Oracle returns NULL for any select list expressions containing columns from the orders table.

#### Table and column names

The names of Oracle identifiers, such as tables and columns, cannot exceed thirty characters in length.

The first character must be a letter, but the rest can be any combination of letters, numerals, dollar signs (`$`), pound signs (`#` ), and underscores ( `_`).

However, if an Oracle identifier is enclosed by double quotes ("), it can contain any combination of legal characters including spaces but excluding quotation marks.

Oracle converts all identifiers to uppercase unless enclosed in double quotes. National characters can also be used when identifiers are quoted.

#### Row ID

The Oracle "row id" pseudocolumn is called ROWID. Oracle ROWIDs are alphanumeric case-sensitive strings. They can be treated as ordinary strings and used to rapidly (re)select rows.

#### Automatic key or sequence generation

Oracle supports "sequence generators". Any number of named sequence generators can be created in a database using the `CREATE SEQUENCE seq_name` SQL command. Each has pseudocolumns called `NEXTVAL` and `CURRVAL`. The typical usage is:

>     INSERT INTO table (k, v) VALUES (seq_name.nextval, ?)

To get the value just inserted you can use:

>     SELECT seq_name.currval FROM DUAL

Oracle does not support automatic key generation such as "auto increment" or "system generated" keys. However they can be emulated using triggers and sequence generators.

For example:

>     CREATE TRIGGER trigger_name
>       BEFORE INSERT ON table_name FOR EACH ROW
>       DECLARE newid integer;
>     BEGIN
>       IF (:NEW.key_field_name IS NULL)
>       THEN
>         SELECT sequence_name.NextVal INTO newid FROM DUAL;
>         :NEW.key_field_name := newid;
>       END IF;
>     END;

Oracle8i (8.1.0 and above) supports Universal Unique ID number generation, per the IETF Internet-Draft, using the new `SYS_GUID( )` function. `GUID`s are more useful than sequence generators in a distributed database since no two hosts will generate the same `GUID`.

#### Automatic row numbering and row count limiting

The `ROWNUM` pseudocolumn can be used to sequentially number selected rows (starting at 1). Sadly, however, Oracle's `ROWNUM` has some frustrating limitations. Refer to the Oracle SQL documentation.

#### Positioned updates and deletes

Oracle does not support positioned updates or deletes.

#### Parameter Binding

Parameter binding is directly supported by Oracle. Both the `?` and `:1` style of placeholders are supported. The `:name` style is also supported, but is not portable.

The `bind_ param()` method `TYPE` attribute can be used to indicate the type a parameter should be bound as. These SQL types are bound as `VARCHAR2:` `SQL_NUMERIC`, `SQL_DECIMAL`, `SQL_INTEGER`, `SQL_SMALLINT`, `SQL_FLOAT`, `SQL_REAL`, `SQL_DOUBLE`, and `SQL_VARCHAR`. Oracle will automatically convert from `VARCHAR2` to the required type.[74]

> [74]Working with strings and letting Oracle handle the conversions actually has many benefits. Oracle's packed decimal numeric format is compact, fast, and has far larger scale and precision than Perl's own numeric values.

The `SQL_CHAR` type is bound as a `CHAR,` thus enabling fixed-width, blank-padded comparison semantics.

The `SQL_BINARY` and `SQL_VARBINARY` types are bound as `RAW`. `SQL_LONGVARBINARY` is bound as `LONG` `RAW` and `SQL_LONGVARCHAR` as `LONG`.

Unsupported values of the `TYPE` attribute generate a warning.

Refer to the `DBD::Oracle` documentation for details of how to bind `LOB`s and `CURSOR`s.

#### Stored Procedures

Oracle stored procedures are implemented in the Oracle PL/SQL language.[75]

> [75]A procedural extension to SQL that supports variables, control flow, packages, exceptions, etc. With Oracle8i, stored procedures can also be implemented in Java.

The `DBD::Oracle` module can be used to execute a block of PL/SQL code by starting it with a `BEGIN` and ending it with an `END;`. PL/SQL blocks are used to call stored procedures. Here's a simple example that calls a stored procedure called "foo" and passes it two parameters:

>     $sth = $dbh->prepare("BEGIN foo(:1, :2); END;");
>     $sth->execute("Baz", 24);

Here's a more complex example that shows a stored procedure being called with two parameters and returning the return value of the procedure. The second parameter of the procedure is defined as `IN` `OUT` so we bind that using `bind_ param_inout()` to enable it to update the Perl variable:

>     $sth = $dbh->prepare("BEGIN :result = func_name(:id, :changeme); END;");
>     $sth->bind_param(":id", "FooBar");
>     my ($result, $changeme) = (41, 42);
>     $sth->bind_param_inout(":result",  \$result,  100);
>     $sth->bind_param_inout(":changeme", \$changeme, 100);
>     $sth->execute();
>     print "func_name returned '$result' and updated changeme to '$changeme'\n";

#### Table Metadata

`DBD::Oracle` supports the `table_info()` method.

The `ALL_TABLES` view contains detailed information about all tables in the database, one row per table.

The `ALL_TAB_COLUMNS` view contains detailed information about all columns of all the tables in the database, one row per table.

The `ALL_INDEXES` view contains detailed information about all indexes in the database, including primary keys, one row per index.

The `ALL_IND_COLUMNS` view contains information about the columns that make up each index.

(Note that for all these views, fields containing statistics derived from the actual data in the corresponding table are updated only when the SQL `ANALYSE` command is executed for that table.)

#### Driver-Specific Attributes and Methods

`DBD::Oracle` has no significant driver-specific database or statement handle attributes.

The following private methods are supported:

`plsql_errstr`

  >     $plsql_errstr = $dbh->func('plsql_errstr');

  Returns error text from the `USER_ERRORS` table.

`dbms_output_enable`

  >     $dbh->func('dbms_output_enable');

  Enables the `DBMS_OUTPUT` package. The `DBMS_OUTPUT` package is typically used to receive trace and informational messages from stored procedures.

`dbms_output_get`

  >     $msg  = $dbh->func('dbms_output_get');
  >     @msgs = $dbh->func('dbms_output_get');

  Gets a single line or all available lines using `DBMS_OUTPUT.GET_LINE`.

`dbms_output_put`

  >     $msg  = $dbh->func('dbms_output_put', @msgs);

  Puts messages using `DBMS_OUTPUT.PUT_LINE`.

DBD::Pg |

#### General Information

#### Driver version

`DBD::Pg` version 0.91

#### Feature summary

>     Transactions                           Yes
>     Locking                                Yes, implicit and explicit
>     Table joins                            Yes, inner only
>     LONG/LOB datatypes                     Yes, max size depends on filesystem
>     Statement handle attributes available  After execute(  )
>     Placeholders                           Yes, "?" and ":1" styles (native)
>     Stored procedures                      No
>     Bind output values                     No
>     Table name letter case                 Insensitive, stored as lowercase
>     Field name letter case                 Insensitive, stored as lowercase
>     Quoting of otherwise invalid names     Yes, via double quotes
>     Case-insensitive "LIKE" operator       No, but has "~*" case-insensitive regex
>                                            match
>     Server table ROW ID pseudocolumn       Yes, "oid"
>     Positioned update/delete               No
>     Concurrent use of multiple handles     Unrestricted

#### Author and contact details

The driver author is Edmund Mergl. He can be contacted via the dbi-users mailing list.

#### Supported database versions and options

The DBD-Pg-0.91 module supports PostgreSQL 6.4. For futher information please refer to:

> http://www.postgresql.org

#### Differences from the DBI Specification

`DBD::Pg` does not fully parse the statement until it's executed. Thus, attributes like `$sth->{NUM_OF_FIELDS}` are not available until after `$sth->execute( )` has been called. This is valid behavior, but it is important to note when porting applications originally written for other drivers.

#### Connect Syntax

The `DBI->connect()` Data Source Name, or DSN, can be one of the following:

>     dbi:Pg:dbname=$dbname
>     dbi:Pg:dbname=$dbname;host=$host;port=$port;options=$options;tty=$tty

All parameters, including the userid and password parameter of the `connect` command, have a hard-coded default that can be overridden by setting appropriate environment variables.

There are no driver-specific attributes for the `DBI->connect()` method.

`DBD::Pg` supports an unlimited number of concurrent database connections to one or more databases.

#### Datatypes

#### Numeric data handling

PostgreSQL supports the following numeric types:

PostgreSQL | Range
--- | ---
int2 | -32768 to +32767
int4 | -2147483648 to +2147483647
float4 | 6 decimal places
float8 | 15 decimal places

Some platforms also support the int8 type. `DBD::Pg` always returns all numbers as strings.

#### String data handling

PostgreSQL supports the following string datatypes:

>     CHAR            single character
>     CHAR(size)      fixed length blank-padded
>     VARCHAR(size)   variable length with limit
>     TEXT            variable length

All string datatypes have a limit of 4096 bytes. The `CHAR` type is fixed length and blank padded.

There is no special handling for data with the eighth bit set. They are stored unchanged in the database. None of the character types can store embedded nulls and Unicode is not formally supported.

Strings can be concatenated using the `||` operator.

#### Date data handling

PostgreSQL supports the following date/time datatypes:

Datatype | Storage | Recommendation | Description
--- | --- | --- | ---
abstime | 4 bytes | original date and time | limited range
date | 4 bytes | SQL92 type | wide range
datetime | 8 bytes | best general date and time | wide range, high precision
interval | 12 bytes | SQL92 type | equivalent to timespan
reltime | 4 bytes | original time interval | limited range, low precision
time | 4 bytes | SQL92 type | wide range
timespan | 12 bytes | best general time interval | wide range, high precision
timestamp | 4 bytes | SQL92 type | limited range

Datatype | Range |  | Resolution
--- | --- | --- | ---
abstime | 1901-12-14 | 2038-01-19 | 1 sec
date | 4713 B.C. | 32767 A.D. | 1 day
datetime | 4713 B.C. | 1465001 A.D. | 1 microsec
interval | -178000000 years | +178000000 years | 1 microsec
reltime | -68 years | +68 years | 1 sec
time | 00:00:00:00 | 23:59:59:99 | 1 microsec
timespan | -178000000 years | 178000000 years | 1 microsec
timestamp | 1901-12-14 | 2038-01-19 | 1 sec

PostgreSQL supports a range of date formats:

Name | Example
--- | ---
ISO | 1997-12-17 0:37:16-08
SQL | 12/17/1997 07:37:16.00 PST
Postgres | Wed Dec 17 07:37:16 1997 PST
European | 17/12/1997 15:37:16.00 MET
NonEuropean | 12/17/1997 15:37:16.00 MET
US | 12/17/1997 07:37:16.00 MET

The default output format does not depend on the client/server locale. It depends on, in increasing priority: the `PGDATESTYLE` environment variable at the server, the `PGDATESTYLE` environment variable at the client, and the `SET` `DATESTYLE` SQL command.

All of the formats described above can be used for input. A great many others can also be used. There is no specific default input format. If the format of a date input is ambiguous then the current `DATESTYLE` is used to help disambiguate.

If you specify a date/time value without a time component, the default time is 00:00:00 (midnight). To specify a date/time value without a date is not allowed. If a date with a two-digit year is input, then if the year was less than 70, add 2000; otherwise, add 1900.

The current date/time is returned by the keyword ``now`' or ``current`' , which has to be cast to a valid datatype. For example:

>     SELECT 'now'::datetime

PostgreSQL supports a range of date/time functions for converting between types, extracting parts of a date/time value, truncating to a given unit, etc. The usual arithmetic can be performed on date and interval values, e.g., date-date=interval, etc.

The following SQL expression can be used to convert an integer "seconds since 1-jan-1970 GMT" value to the corresponding database date/time:

>     DATETIME(unixtime_field)

and to do the reverse:

>     DATE_PART('epoch', datetime_field)

The server stores all dates internally in GMT. Times are converted to local time on the database server before being sent to the client frontend, hence by default are in the server time zone.

The `TZ` environment variable is used by the server as default time zone. The `PGTZ` environment variable on the client side is used to send the time zone information to the backend upon connection. The SQL `SET TIME ZONE` command can set the time zone for the current session.

#### LONG/BLOB data handling

PostgreSQL handles `BLOB`s using a so-called "large objects" type. The handling of this type differs from all other datatypes. The data are broken into chunks, which are stored in tuples in the database. Access to large objects is given by an interface which is modelled closely after the Unix file system. The maximum size is limited by the file size of the operating system.

If you just select the field, you get a "large object identifier" and not the data itself. The `LongReadLen` and `LongTruncOk` attributes are not implemented because they don't make sense in this case. The only method implemented by the driver is the undocumented DBI method `blob_read( )`.

#### Other data handling issues

The `DBD::Pg` driver supports the `type_info( )` method.

PostgreSQL supports automatic conversions between datatypes wherever it's reasonable.

#### Transactions, Isolation, and Locking

PostgreSQL supports transactions. The current default isolation transaction level is Serializable and is currently implemented using table-level locks. Both may change. No other isolation levels for transactions are supported.

With `AutoCommit` on, a query never places a lock on a table. Readers never block writers, and writers never block readers. This behavior changes whenever a transaction is started (with `AutoCommit` off). Then a query induces a shared lock on a table and blocks anyone else until the transaction has been finished.

The `LOCK` `TABLE` `table_name` statement can be used to apply an explicit lock on a table. This works only inside a transaction (with `AutoCommit` off).

To ensure that a table being selected does not change before you make an update later in the transaction, you must explicitly lock it with a `LOCK` `TABLE` statement before executing the select.

#### SQL Dialect

#### Case sensitivity of LIKE operator

PostgreSQL has the following string matching operators:

Glyph | Description | Example
--- | --- | ---
~~ | Same as SQL "LIKE" operator | `'scrappy,marc' ~~ '%scrappy%'`
!~~ | Same as SQL "NOT LIKE" operator | `'bruce' !~~ '%al%'`
~ | Match (regex), case-sensitive | `'thomas' ~ '.*thomas.*'`
~* | Match (regex), case-insensitive | `'thomas' ~* '.*Thomas.*'`
!~ | Doesn't match (regex), case-sensitive | `'thomas' !~ '.*Thomas.*'`
!~* | Doesn't match (regex), case-insensitive | `'thomas' !~ '.*vadim.*'`

#### Table join syntax

Outer joins are not supported. Inner joins use the traditional syntax.

#### Table and column names

The maximum size of table and column names cannot exceed 31 charaters in length. Only alphanumeric characters can be used; the first character must be a letter.

If an identifier is enclosed by double quotes (" ), it can contain any combination of characters except double quotes.

PostgreSQL converts all identifiers to lowercase unless enclosed in double quotes. National character set characters can be used, if enclosed in quotation marks.

#### Row ID

The PostgreSQL "row id" pseudocolumn is called oid, object identifier. It can be treated as a string and used to rapidly (re)select rows.

#### Automatic key or sequence generation

PostgreSQL does not support automatic key generation such as "auto increment" or "system generated" keys.

However, PostgreSQL does support "sequence generators." Any number of named sequence generators can be created in a database. Sequences are used via functions called `NEXTVAL` and `CURRVAL`. The typical usage is:

>     INSERT INTO table (k, v) VALUES (NEXTVAL('seq_name'), ?);

To get the value just inserted, you can use the corresponding `currval( )` SQL function in the same session, or:

>     SELECT last_value FROM seq_name

#### Automatic row numbering and row count limiting

Neither automatic row numbering nor row count limitations are supported.

#### Positioned updates and deletes

PostgreSQL does not support positioned updates or deletes.

#### Parameter Binding

Parameter binding is emulated by the driver. Both the `?` and `:1` style of placeholders are supported.

The `TYPE` attribute of the `bind_ param()` method may be used to influence how parameters are treated. These SQL types are bound as `VARCHAR: SQL_NUMERIC`, `SQL_DECIMAL`, `SQL_INTEGER`, `SQL_SMALLINT`, `SQL_FLOAT`, `SQL_REAL`, `SQL_DOUBLE`, and `SQL_VARCHAR`.

The `SQL_CHAR` type is bound as a `CHAR`, thus enabling fixed-width, blank-padded comparison semantics.

Unsupported values of the `TYPE` attribute generate a warning.

#### Stored Procedures

`DBD::Pg` does not support stored procedures.

#### Table Metadata

`DBD::Pg` supports the `table_info()` method.

The `pg_attribute` table contains detailed information about all columns of all the tables in the database, one row per table.

The `pg_index` table contains detailed information about all indexes in the database, including primary keys, one row per index.

#### Driver-Specific Attributes and Methods

There are no significant `DBD::Pg` driver-specific database handle attributes.

`DBD::Pg` has the following driver-specific statement handle attributes:

`pg_size`

  Returns a reference to an array of integer values for each column. The integer shows the storage (not display) size of the column in bytes. Variable length columns are indicated by -1.

`pg_type`

  Returns a reference to an array of strings for each column. The string shows the name of the datatype.

`pg_oid_status`

  Returns the OID of the last `INSERT` command.

`pg_cmd_status`

  Returns the name of the last command type. Possible types are: `INSERT`, `DELETE`, `UPDATE`, and `SELECT`.

`DBD::Pg` has no private methods.

#### Other Significant Database or Driver Features

PostgreSQL offers substantial additional power by incorporating the following four additional basic concepts in such a way that users can easily extend the system: classes, inheritance, types, and functions.

Other features provide additional power and flexibility: constraints, triggers, rules, transaction integrity, procedural languages, and large objects.

It's also free Open Source Software with an active community of developers.

DBD::SearchServer |

#### General Information

#### Driver version

`DBD::SearchServer` version 0.20

This driver was previously known as `DBD::Fulcrum`.

#### Feature summary

>     Transactions                           No
>     Locking                                Yes, implicit and explicit
>     Table joins                            No, but see description below
>     LONG/LOB datatypes                     Yes, up to 2 GB
>     Statement handle attributes available  After execute(  )
>     Placeholders                           Yes, "?" and ":1" styles (emulated)
>     Stored procedures                      No
>     Bind output values                     No
>     Table name letter case                 Insensitive, stored as uppercase
>     Field name letter case                 Insensitive, stored as uppercase
>     Quoting of otherwise invalid names     Yes, via double quotes
>     Case-insensitive "LIKE" operator       Yes, "LIKE"
>     Server table ROW ID pseudocolumn       Yes, "FT_CID"
>     Positioned update/delete               Yes
>     Concurrent use of multiple handles     Unrestricted

#### Author and contact details

The driver author is Davide Migliavacca. He can be contacted via the dbi-users mailing list. Davide Migliavacca has no relationship with PCDOCS/Fulcrum, the maker of SearchServer, and particularly no contact with product support for PCDOCS/Fulcrum customers.

#### Supported database versions and options

The `DBD::SearchServer` module supports PCDOCS/Fulcrum SearchServer, versions 2.x thru 3.5.

Fulcrum SearchServer is a very powerful text-retrieval system with a SQL interface. You should not expect to find a full-fledged SQL RDBMS here. Refer to the product documentation for details about the query language.

For further information about SearchServer, refer to:

> http://www.pcdocs.com

#### Differences from the DBI specification

`DBD::SearchServer` doesn't fully parse the statement until it's executed. Attributes like `$sth->{NUM_OF_FIELDS}` aren't available until after `$sth->execute( )` has been called. This is valid behavior but is important to note when porting applications originally written for other drivers.

#### Connect Syntax

Under Unix, you may specify where SearchServer will find the database tables by using a set of environment variables: `FULSEARCH`, `FULCREATE`, and `FULTEMP`. So the connect string is always just:

>     dbi:SearchServer:

Under WIN32, you may use the fully qualified DSN syntax using the ODBC data source name as the third component of the connect string:

>     dbi:SearchServer:DSN

There are no driver-specific attributes for the `DBI->connect()` method.

`DBD::SearchServer` supports an unlimited number of concurrent database connections to the same server.

#### Datatypes

#### Numeric data handling

SearchServer has two numeric datatypes: `INTEGER` and `SMALLINT`. `INTEGER` (or `INT`) is an unsigned 32-bit binary integer with 10 digits of precision. `SMALLINT` is a signed 16-bit binary integer with 5 digits of precision.

#### String data handling

SearchServer supports the following string datatypes:

>     CHAR(size)
>     VARCHAR(size)
>     APVARCHAR(size)

A `CHAR` column is of fixed size, whereas a `VARCHAR` column can be of varying length up to the specified maximum size. If the size is not specified, it defaults to 1. The maximum size for a `CHAR` or `VARCHAR` column is 32,767.

`APVARCHAR` is a special datatype. You can have at most one `APVARCHAR` column per table; it is designed to contain the full text of the document to be indexed and it is used in queries to retrieve the text. It is eventually modified to identify spots where the query matched. The maximum length of the `APVARCHAR` column is 2,147,483,647.

The CHAR type is fixed-length and blank-padded to the right.

SearchServer has its own conversion functionality for national language character sets. Basically, it treats all text as being specified in one of three internal character sets (FTICS). It is up to the application to use character sets consistently. The document readers (software that is used by SearchServer to actually access documents when indexing) are responsible for translating from other characters sets to FTICS. A number of "translation" filters are distributed with the product.

ISO Latin 1 (8859-1) is supported. See the "Character Sets" section of the SearchSQL Reference Manual for more details on character set issues.

#### Date data handling

SearchServer supports only a `DATE` datatype. A `DATE` can have any value from January 1, 100 AD to December 31, 2047 AD with one-day resolution. Rows in tables have an automatic read-only `FT_TIMESTAMP` column with a better resolution, but it is not of a `DATE` type (it is an `INTEGER`). Also, only date literals can be used with `DATE` columns.

The date format is `YYYY-MM-DD` (ISO standard). There are provisions for other formats, but their use is discouraged.

Only the ISO date format is recognized for input.

If a two-digit year value is entered, then 1900 is added to the value. However, this isn't supported functionality, for good reason.

No date/time arithmetic or functions are provided, and there is no support for time zones.

#### LONG/BLOB data handling

The `APVARCHAR` type can hold up to 2 GB.

`LongReadLen` and `LongTruncOk` are ignored due to very different semantics of the `APVARCHAR` type.

You need to use the undocumented `blob_read( )` method to fetch data from an `APVARCHAR` column. Inserting an `APVARCHAR` column happens indirectly by specifying an external document in the `FT_SFNAME` reserved column. Document data is not really inserted into the tables, it is indexed. Later, however, you can fetch the document selecting the `APVARCHAR` column.

#### Other data handling issues

The `DBD::SearchServer` driver does not support the `type_info( )` method.

#### Transactions, Isolation, and Locking

`DBD::SearchServer` does not support transactions.

Locking is performed based on the characteristics of the table, set at creation time or modified later with an external utility, `ftlock`.

By default, `ROWLOCKING` is applied, which applies "transient" locks during normal operations including select, searched update, and delete. These locks should not prevent reading the affected rows, but will block additional concurrent modifications, and prevent reindexing of the locked rows.

If set to `NOLOCKING`, no locking will be performed on that table by the engine, meaning that data integrity is left for the application to manage. Please read the documentation carefully before playing with these parameters; there is additional feedback with the `PERIODIC` or `IMMEDIATE` indexing mode.

Rows returned by a `SELECT` statement can be locked to prevent them from being changed by another transaction, by appending `FOR` `UPDATE` to the select statement.

There is no explicit table lock facility. You can prevent a table schema being modified, dropped, or even reindexed using `PROTECT` `TABLE`, but this does not include row-level modifications, which are still allowed. `UNPROTECT` `TABLE` restores normal behavior.

#### SQL Dialect

#### Case sensitivity of LIKE operator

The `LIKE` operator is not case-sensitive.

#### Table join syntax

SearchServer does not really support joins, however it does support a kind of view mechanism.

With views, tables must be located on the same node and have the same schema. Only read-only access is granted with views, and they have to be described using a special syntax file. Please refer to the "Data Administration and Preparation" manual for more information on views.

#### Table and column names

Letters, numbers, and underscores ( `_`) are valid characters in identifiers. The maximum size of table and column names is not known at this time.

SearchServer converts all identifiers to uppercase. Table and column names are not case-sensitive. National characters can be used in identifier names.

#### Row ID

The SearchServer "row id" pseudocolumn is called `FT_CID` and is of the `INTEGER` datatype. `FT_CID` can be used in a WHERE clause, but only with the `=` operator.

#### Automatic key or sequence generation

SearchServer does not support automatic key generation such as "auto increment" or "system generated" keys. However, the integer `FT_CID` pseudocolumn is not reissued when rows are deleted.

There is no support for sequence generators.

#### Automatic row numbering and row count limiting

Neither automatic row numbering nor row count limitations are supported.

#### Positioned updates and deletes

Positioned updates and deletes are supported using the `WHERE` `CURRENT` `OF` syntax. For example:

>     $dbh->do("UPDATE ... WHERE CURRENT OF $sth->{CursorName}");

#### Parameter Binding

Both the `?` and `:1` style of placeholders are supported by driver emulation.

The `TYPE` attribute to `bind_ param()` is ignored, so no warning is generated for unsupported values.

#### Stored Procedures

There are no stored procedures or functions in SearchServer.

#### Table Metadata

`DBD::SearchServer` supports the `table_info()` method.

The `COLUMNS` system table contains detailed information about all columns of all the tables in the database, one row per column. The `COLUMNS` system table uses the `INDEX_MODE` column to identify indexed columns and which indexing mode is used for them.

#### Driver-Specific Attributes and Methods

`DBD::SearchServer` has no driver-specific database handle attributes. It does have one driver-specific statement handle attribute:

`ss_last_row_id`

  This attribute is read-only and is valid after an `INSERT`, `DELETE`, or `UPDATE` statement. It will report the `FT_CID` (row ID) of the last affected row in the statement. You'll have to prepare/execute the statement (as opposed to simply `do()`-ing it) in order to fetch the attribute.

There are no private methods.

DBD::Sybase -- For Sybase and Microsoft SQL Server |

#### General Information

#### Driver version

`DBD::Sybase` version 0.14

#### Feature summary

>     Transactions                           Yes
>     Locking                                Yes, implicit and explicit
>     Table joins                            Yes, inner and outer
>     LONG/LOB datatypes                     Yes, up to 2 GB
>     Statement handle attributes available  After execute(  )
>     Placeholders                           Yes, "?" style (native), see text below
>     Stored procedures                      Yes
>     Bind output values                     No, all values returned via fetch methods
>     Table name letter case                 Sensitive, stored as defined, configurable
>     Field name letter case                 Sensitive, stored as defined, configurable
>     Quoting of otherwise invalid names     Yes, via double quotes
>     Case-insensitive "LIKE" operator       No
>     Server table ROW ID pseudocolumn       No
>     Positioned update/delete               No
>     Concurrent use of multiple handles     Statement handles restricted, see below

#### Author and contact details

The driver author is Michael Peppler. He can be contacted via the dbi-users mailing list, or at mpeppler@peppler.org.

#### Supported database versions and options

The `DBD::Sybase` module supports Sybase 10.x and 11.x, and offers limited support for accessing Sybase 4.x and Microsoft MS-SQL servers, assuming availability of Sybase OpenClient, or the FreeTDS libraries.

The standard release of MS-SQL 7 can not be accessed using the Sybase libraries, but can be used using the FreeTDS libraries. There is a patch for MS-SQL 7 to allow Sybase clients to connect:

> http://support.microsoft.com/support/kb/articles/q239/8/83.asp

The FreeTDS libraries (www.freetds.org) is an Open Source effort to reverse engineer the TDS (Tabular Data Stream) protocol that both Sybase and Microsoft use. FreeTDS is still in alpha, but `DBD::Sybase` builds cleanly against the latest release and suppports most functions (apart from `?`-style placeholders).

Here are some URLs to more database/driver specific information:

> http://www.sybase.com

> http://techinfo.sybase.com

> http://sybooks.sybase.com

> http://www.microsoft.com/sql

> http://www.freetds.org

#### Differences from the DBI specification

The `LongReadLen` and `LongTruncOk` attributes are not supported.

Note that `DBD::Sybase` does not fully parse the statement until it's executed. Thus, attributes like `$sth->{NUM_OF_FIELDS}` are not available until after `$sth->execute( )` has been called. This is valid behavior but is important to note when porting applications originally written for other drivers.

#### Connect Syntax

The `DBI->connect()` Data Source Name, or DSN, has the following format:

>     dbi:Sybase:attrs

where `attrs` is a semicolon-separated list of key=value pairs. Valid attributes include:

server

  Specifies the Sybase server to connect to.

database

  Specifies the database within the server that should be made the default database for this session (via `USE database`).

charset

  Specifies the client character set to use. Useful if the client's default character set is different from the server. Using this will enable automatic character conversion from one character set to the other.

`DBD::Sybase` supports an unlimited number of concurrent database connections to one or more databases.

It is not normally possible for Sybase clients to prepare/execute a new statement handle while still fetching data from another statement handle that is associated with the same database handle. However, `DBD::Sybase` emulates this process by opening a new connection that will automatically be closed when the new statement handle is destroyed. You should be aware that there are some subtle but significant transaction issues with this approach.

#### Datatypes

#### Numeric data handling

The driver supports `INTEGER`, `SMALLINT`, `TINYINT`, `MONEY`, `SMALLMONEY`, `FLOAT`, `REAL`, `DOUBLE`, `NUMERIC(p,s)`, and `DECIMAL(p,s)`.

`INTEGER` is always a 32-bit int, `SMALLINT` is 16-bit, and `TINYINT` is 8-bit. All others except the `NUMERIC`/`DECIMAL` datatypes are hardware specific. Precision for `NUMERIC`/`DECIMAL` is from 1 to 38, and scale is from to 38.

`NUMERIC`/`DECIMAL` values are returned as Perl strings by default, even if the scale is and the precision is small enough to fit in an integer value. All other numbers are returned in native format.

#### String data handling

`DBD::Sybase` supports `CHAR`, `VARCHAR`, `BINARY`, and `VARBINARY`, all limited to 255 characters in length. The `CHAR` type is fixed-length and blank-padded.

Sybase automatically converts `CHAR` and `VARCHAR` data between the character set of the server (see the `syscharset` system table) and the character set of the client, defined by the locale setting of the client. The `BINARY` and `VARBINARY` types are not converted. UTF-8 is supported.

See the "OpenClient International Developer's Guide" in the Sybase OpenClient manuals for more on character set issues.

Strings can be concatenated using the SQL `+` operator.

#### Date data handling

Sybase supports the `DATETIME` and `SMALLDATETIME` values. A `DATETIME` can have a value from Jan 1 1753 to Dec 31, 9999 with a 300th of a second resolution. A `SMALLDATETIME` has a range of Jan 1 1900 to Jun 6 2079 with a one-minute resolution.

The current date on the server is obtained with the `GETDATE( )` SQL function.

The Sybase date format depends on the locale settings for the client. The default date format is based on the "C" locale:

>     Feb 16 1999 12:07PM

In this same locale, Sybase understands several input formats in addition to the one above:

>     2/16/1998 12:07PM
>     1998/02/16 12:07
>     1998-02-16 12:07
>     19980216 12:07

If the time portion is omitted, it is set to 00:00. If the date portion is omitted, it is set to Jan 1 1900. If the century is omitted, it is assumed to be 2000 if year < 50, and 1900 if year >= 50.

You can use the special `_date_fmt( )` private method (accessed via `$dbh->func( )`) to change the date input and output format. The formats are based on Sybase's standard conversion routines. The following subset of available formats has been implemented:

>     LONG        - Nov 15 1998 11:30:11:496AM
>     SHORT       - Nov 15 1998 11:30AM
>     DMY4_YYYY   - 15 Nov 1998
>     MDY1_YYYY   - 11/15/1998
>     DMY1_YYYY   - 15/11/1998
>     HMS         - 11:30:11

Use the `CONVERT( )` SQL function to convert date and time values from other formats. For example:

>     UPDATE a_table
>         SET date_field = CONVERT(datetime_field, '1999-02-21', 105)

`CONVERT( )` is a generic conversion function that can convert to and from most datatypes. See the `CONVERT( )` function in Chapter 2 of the Sybase Reference Manual.

Arithmetic on date/time types is done on dates via the `DATEADD( )`, `DATEPART( )`, and `DATEDIFF( )` Transact SQL functions. For example:

>     SELECT DATEDIFF(ss, date1, date2)

returns the difference in seconds between `date1` and `date2`.

Sybase does not understand time zones at all, except that the `GETDATE( )` SQL function returns the date in the time zone that the server is running in (via `localtime`).

The following SQL expression can be used to convert an integer "seconds since 1-jan-1970" value ("Unix time") to the corresponding database date/time:

>     DATEADD(ss, unixtime_field, 'Jan 1 1970')

Note however that the server does not understand time zones, and will therefore give the local Unix time on the server, and not the correct value for the GMT time zone.

If you know that the server runs in the same time zone as the client, you can use:

>     use Time::Local;
>     $time_to_database = timegm(localtime($unixtime));

to convert the Unix time value before sending it to Sybase.

To do the reverse, converting from a database date/time value to Unix time, you can use:

>     DATEDIFF(ss, 'Jan 1 1970', datetime_field)

The same GMT versus localtime caveat applies in this case. If you know that the server runs in the same time zone as the client, you can convert the returned value to the correct GMT-based value with this Perl expression:

>     use Time::Local;
>     $time = timelocal(gmtime($time_from_database));

#### LONG/BLOB data handling

Sybase supports an `IMAGE` and a `TEXT` type for `LONG`/`BLOB` data. Each type can hold up to 2 GB of binary data, including `nul` characters. The main difference between an `IMAGE` and a `TEXT` column lies in how the client libraries treat the data on input and output. `TEXT` data is entered and returned "as is." `IMAGE` data is returned as a long hex string, and should be entered in the same way.

`LongReadLen` and `LongTrunkOk` attributes have no effect. The default limit for `TEXT`/`IMAGE` data is 32 KB, but this can be changed by the `SET` `TEXTSIZE` Transact-SQL command.

Bind parameters can not be used to insert `TEXT` or `IMAGE` data to Sybase.

#### Other data handling issues

The `DBD::Sybase` driver does not support the `type_info( )` method yet.

Sybase does not automatically convert numbers to strings or strings to numbers. You need to explicitly call the `CONVERT` SQL function. However, placeholders don't need special handling because `DBD::Sybase` knows what type each placeholder needs to be.

#### Transactions, Isolation, and Locking

`DBD::Sybase` supports transactions. The default transaction isolation level is READ COMMITTED.

Sybase supports READ COMMITED, READ UNCOMMITED, and SERIALIZABLE isolation levels. The level be changed per-connection or per-statement by executing `SET TRANSACTION_ISOLATION LEVEL x`, where `x` is for READ UNCOMMITED, 1 for READ COMMITED, and 3 for SERIALIZABLE.

By default, a READ query will acquire a shared lock on each page that it reads. This will allow any other process to read from the table, but will block any process trying to obtain an exclusive lock (for update). The shared lock is only maintained for the time the server needs to actually read the page, not for the entire length of the `SELECT` operation. (11.9.2 and later servers have various additional locking mechanisms.)

There is no explicit `LOCK` `TABLE` statement. Appending `WITH` `HOLDLOCK` to a `SELECT` statement can be used to force an exclusive lock to be acquired on a table, but is rarely needed.

The correct way to do a multi-table update with Sybase is to wrap the entire operation in a transaction. This will ensure that locks will be acquired in the correct order, and that no intervening action from another process will modify any rows that your operation is currently modifying.

#### SQL Dialect

#### Case sensitivity of LIKE operator

The `LIKE` operator is case-sensitive.

#### Table join syntax

Outer joins are supported using the `=*` (right outer join) and `*=` (left outer join) operators:

>     SELECT customers.customer_name, orders.order_date
>     FROM customers, orders
>     WHERE customers.cust_id =* orders.cust_id

For all rows in the customer's table that have no matching rows in the orders table, Sybase returns NULL for any select list expressions containing columns from the orders table.

#### Table and column names

The names of identifiers, such as tables and columns, cannot exceed thirty characters in length.

The first character must be an alphabetic character (as defined by the current server character set) or an underscore ( `_` ). Subsequent characters can be alphabetic, and may include currency symbols, `@`, `#`, and `_`. Identifiers can't include embedded spaces or the `%`, `!`, `^`, `*`, or `.` symbols. In addition, identifiers must not be on the "reserved word" list (see the Sybase documentation for a complete list).

Table names or column names may be quoted if the `set` `quoted_identifier` option is turned on. This allows the user to get around the reserved word limitation. When this option is set, character strings enclosed in double quotes are treated as identifiers, and strings enclosed in single quotes are treated as literal strings.

By default identifiers are case-sensitive. This can be turned off by changing the default sort order for the server.

National characters can be used in identifier names without quoting.

#### Row ID

Sybase does not support a pseudo "row ID" column.

#### Automatic key or sequence generation

Sybase supports an `IDENTITY` feature for automatic key generation. Declaring a table with an `IDENTITY` column will generate a new value for each insert. The values assigned always increase but are not guaranteed to be sequential.

To fetch the value generated and used by the last insert, you can:

>     SELECT @@IDENTITY

Sybase does not support sequence generators, although ad hoc stored procedures to generate sequence numbers are quite easy to write.[76]

> [76]See http://techinfo.sybase.com/css/techinfo.nsf/DocID/ID=860 for a complete explanation of the various possibilities.

#### Automatic row numbering and row count limiting

Neither automatic row numbering nor row count limitations are supported.

#### Positioned updates and deletes

Sybase does not support positioned updates or deletes.

#### Parameter Binding

Parameter binding is directly suported by Sybase. However, there are two downsides that you should be aware of.

Firstly, `DBD::Sybase` creates an internal stored procedure for each `prepare()` call that includes `?` style parameters. These stored procedures live in the tempdb database, and are only destroyed when the connection is closed. It is quite possible to run out of tempdb space if a lot of `prepare()` calls with placeholders are being made in a script.

Secondly, because all the temporary stored procedures are created in tempdb, this causes a potential hot spot due to the locking of system tables in tempdb. This performance problem may be removed in an upcoming release of Sybase (possibly 11.9.4 or 12.0).

The `:1` placeholder style is not supported and the `TYPE` attribute to `bind_ param()` is currently ignored, so unsupported values don't generate a warning. Finally, trying to bind a `TEXT` or `IMAGE` datatype will fail.

#### Stored Procedures

Sybase stored procedures are written in Transact-SQL, which is Sybase's procedural extension to SQL.

Stored procedures are called exactly the same way as regular SQL, and can return the same types of results (i.e., a `SELECT` in the stored procedure can be retrieved with `$sth->fetch()`).

If the stored procedure returns data via `OUTPUT` parameters, then these must be declared first:

>     $sth = $dbh->prepare(qq[
>        declare \@name varchar(50)
>        exec getName 1234, \@name output
>     ]);

Stored procedures can't be called with `bind` (`?`) parameters. So the following code would be illegal:

>     $sth = $dbh->prepare("exec my_proc ?");  # illegal
>     $sth->execute($foo);

Use this code instead:

>     $sth = $dbh->prepare("exec my_proc '$foo'");
>     $sth->execute();

Because Sybase stored procedures almost always return more than one result set, you should always make sure to use a loop until `syb_more_results` is 0:

>     do {
>       while($data = $sth->fetch) {
>          ...
>       }
>     } while($sth->{syb_more_results});

#### Table Metadata

`DBD::Sybase` supports the `table_info()` method.

The `syscolumns` table has one row per column per table. See the definitions of the Sybase system tables for details. However, the easiest method to obtain table metadata is to use the `sp_help` stored procedure.

The easiest way to get detailed information about the indexes of a table is to use the `sp_helpindex` (or `sp_helpkey`) stored procedure.

#### Driver-Specific Attributes and Methods

`DBD::Sybase` has the following driver-specific database handle attributes:

`syb_show_sql`

  If set, then the current statement is included in the string returned by `$dbh->errstr`.

`syb_show_eed`

  If set, then extended error information is included in the string returned by `$dbh->errstr`. Extended error information includes the index causing a duplicate insert to fail, for example.

`DBD::Sybase` has the following driver-specific statement handle attributes:

`syb_more_results`

  Described elsewhere in this document.

`syb_result_type`

  Returns the numeric result type of the current result set. Useful when executing stored procedures to determine what type of information is currently fetchable (normal select rows, output parameters, status results, etc.).

One private method is provided:

`_date_fmt`

  Sets the default date conversion and display formats. See the description elsewhere in this document.

#### Other Significant Database or Driver Features

Sybase and `DBD::Sybase` allow multiple statements to be prepared with one call and then executed with one call. The results are fed back to the client as a stream of tabular data. Stored procedures can also return a stream of multiple data sets. Each distinct set of results is treated as a normal single result set, so `fetch()` returns `undef` at the end of each set. To see if there are more data sets to follow, the `syb_more_results` attribute can be checked. Here is a typical loop making use of this Sybase-specific feature:

>     do {
>       while($d = $sth->fetch) {
>         ... do something with the data
>       }
>     } while($sth->{syb_more_results});

Sybase also has rich and powerful stored procedure and trigger functionality and encourages you to use them.

DBD::XBase |

#### General Information

#### Driver version

`DBD::XBase` version 0.145

#### Feature summary

>     Transactions                           No
>     Locking                                No
>     Table joins                            No
>     LONG/LOB datatypes                     Yes, up to 2 GB
>     Statement handle attributes available  After execute(  )
>     Placeholders                           Yes, "?" and ":1" styles (emulated)
>     Stored procedures                      No
>     Bind output values                     No
>     Table name letter case                 Sensitive, stored as defined
>     Field name letter case                 Insensitive, stored as uppercase
>     Quoting of otherwise invalid names     No
>     Case-insensitive "LIKE" operator       Yes, "LIKE"
>     Server table ROW ID pseudocolumn       No
>     Positioned update/delete               No
>     Concurrent use of multiple handles     Unrestricted

#### Author and contact details

The driver author is Jan Pazdziora. He can be contacted at adelton@fi.muni.cz or via the dbi-users mailing list.

#### Supported database versions and options

The `DBD::XBase` module supports dBaseIII and IV and Fox* flavors of dbf files, including their dbt and fpt memo files.

Very comprehensive information about the XBase format, along with many references, can be found at:

> http://www.e-bachmann.dk/docs/xbase.htm

#### Differences from the DBI specification

`DBD::XBase` does not fully parse the statement until it is executed. Thus attributes like `$sth->{NUM_OF_FIELDS}` are not available until after `$sth->execute( )` has been called. This is valid behavior but is important to note when porting applications written originally for other drivers.

#### Connect Syntax

The `DBI->connect()` Data Source Name, or DSN, should include the directory where the dbf files are located as the third part:

>     dbi:XBase:/path/to/directory

It defaults to the current directory.

There are no driver-specific attributes for the `DBI->connect()` method.

`DBD::XBase` supports an unlimited number of concurrent database connections to one or more databases.

#### Datatypes

#### Numeric data handling

`DBD::XBase` supports generic `NUMBER(p,s)`, `FLOAT(p,s)`, and `INTEGER(l)` types. The maximum scale and precision is limited by Perl's handling of numbers. In the dbf files, the numbers are stored as ASCII strings, binary integers, or floats.

Existing dbf files come with the field types defined in the dbf file header. Numeric types can be either stored as ASCII string or in some binary format. `DBD::XBase` (via `XBase.pm`) parses this information and reads and writes the fields in that format.

When you create a new dbf file via `CREATE TABLE`, the numeric fields are always created in the traditional XBase way, as an ASCII string. (The `XBase.pm` module offers more control over this process.)

Numeric fields are always returned as Perl numeric values, not strings. Consequently, numbers outside of Perl's valid range are not possible. This restriction might be withdrawn in the future.

#### String data handling

`DBD::XBase` has `CHAR(length)` and `VARCHAR(length)` datatypes.

The maximum length is 65535 characters for both types.[77]

> [77]This limit is effective even though the older dBases allowed only 254 characters. Therefore, newly created dbf files might not be portable to older XBase-compatible software.

Both `CHAR` and `VARCHAR` are blank-padded, so `ChopBlanks` applies to both.

Data with the eighth bit set are handled transparently. No national language character set conversions are done. Since the string types can store binary data, Unicode strings can be stored.

#### Date data handling

`DBD::XBase` supports these date and time types:

>     DATE
>     DATETIME
>     TIME

The `DATE` type holds an eight-character string in the format `YYYYMMDD`. Only that format can be used for input and output. `DBD::XBase` doesn't check for validity of the values.

The `DATETIME` and `TIME` types internally store a four-byte integer day value and a four-byte integer seconds value (counting 1/1000's of a second). `DBD::XBase` inputs and outputs these types using a floating-point Unix-style "seconds-since-epoch" value (possibly with decimal part). This might change in the future.

There is no way to get the current date/time, and no SQL date/time functions are supported. There is also no concept of time zones.

#### LONG/BLOB data handling

`DBD::XBase` supports a `MEMO` datatype. `BLOB` can be used as an alias for `MEMO`. Strings up to 2 GB can be stored in `MEMO` fields (for all types of XBase memo files).

With dBaseIII dbt files, the memo field cannot contain a `0x1A` byte. With dBaseIV and Fox* `dbt`/`fpt`s, any character values can be stored.

No special handling is required for fetching or inserting `MEMO` fields. The `LongReadLen` and `LongTruncOk` attributes are currently ignored.

#### Other data handling issues

The `DBD::XBase` driver supports the `type_info( )` method.

`DBD::XBase` supports automatic conversions between datatypes wherever it's reasonable.

#### Transactions, Isolation, and Locking

`DBD::XBase` does not support transactions and does not lock the tables it is working on.

#### SQL Dialect

#### Case sensitivity of LIKE operator

The `LIKE` operator is not case-sensitive.

#### Table join syntax

`DBD::XBase` does not support table joins.

#### Table and column names

The XBase format stores each table as a distinct file. Memo fields are stored in an additional file. The table names are limited by the filesystem's maximum filename length. They are stored and treated as entered. The case-sensitivity depends on the filesystem that the file is stored on.

Column names are limited to eleven characters. They are stored as uppercase, but are not case-sensitive.

Table and field names have to start with letter. Any combination of letters, digits, and underscores may follow. National character sets can be used.

`DBD::XBase` does not support putting quotes around table or column names.

#### Row ID

`DBD::XBase` does not support a "row ID" pseudocolumn.

#### Automatic key or sequence generation

`DBD::XBase` does not support automatic key generation or sequence generators owing to the limitations of the XBase format.

#### Automatic row numbering and row count limiting

Neither automatic row numbering nor row count limitations are supported.

#### Positioned updates and deletes

`DBD::XBase` does not support positioned updates or deletes.

#### Parameter Binding

Parameter binding is implemented in the driver and supports the `?`, `:1`, and `:name` placeholder styles.

The `TYPE` attribute to `bind_ param()` is ignored. Consequently, unsupported values of the `TYPE` attribute do not currently generate a warning.

#### Stored Procedures

Stored procedures are not applicable in the XBase format.

#### Table Metadata

`DBD::XBase` supports the `table_info` method.

There is no way to get detailed information about the columns of a table (at the moment) other than by doing a `SELECT * FROM table` and using the `NAME` and `TYPE` attributes of the statement handle.

Keys and indexes are not currently supported.

#### Driver-Specific Attributes and Methods

`DBD::XBase` has just one driver-specific attribute and that is valid for both database and statement handles:

`xbase_ignorememo`

  Ignore memo files and thus don't fail to read a table where the memo file is missing or corrupt.

`DBD::XBase` has no generally useful private methods.

Copyright © 2001 O'Reilly & Associates. All rights reserved.